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.
This exercise is part of the course
Analyzing Business Data in SQL
Exercise instructions
- 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.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
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;