Bucketing users by revenue
Based on his analysis, Dave identified that $150 is a good cut-off for low-revenue users, and $300 is a good cut-off for mid-revenue users. He wants to find the number of users in each category to tweak Delivr's business model.
Split the users into low, mid, and high-revenue buckets, and return the count of users in each group.
Deze oefening maakt deel uit van de cursus
Analyzing Business Data in SQL
Oefeninstructies
- Store each user ID and the revenue it generates in the
user_revenuesCTE. - Return a table of the revenue groups and the count of users in each group.
Praktische interactieve oefening
Probeer deze oefening eens door deze voorbeeldcode in te vullen.
WITH user_revenues AS (
SELECT
-- Select the user IDs and the revenues they generate
___,
___ AS revenue
FROM meals AS m
JOIN orders AS o ON m.meal_id = o.meal_id
GROUP BY user_id)
SELECT
-- Fill in the bucketing conditions
CASE
WHEN ___ THEN 'Low-revenue users'
WHEN ___ THEN 'Mid-revenue users'
ELSE 'High-revenue users'
END AS revenue_group,
___ AS users
FROM user_revenues
GROUP BY revenue_group;