Bucketing users by orders
Dave is repeating his bucketing analysis on orders to have a more complete profile of each group. He determined that 8 orders is a good cut-off for the low-orders group, and 15 is a good cut-off for the medium orders group.
Send Dave a table of each order group and how many users are in it.
This exercise is part of the course
Analyzing Business Data in SQL
Exercise instructions
- Store each user ID and its count of orders in a CTE named
user_orders. - Set the cut-off point for the low-orders bucket to 8 orders, and set the cut-off point for the mid-orders bucket to 15 orders.
- Count the distinct users in each bucket.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Store each user's count of orders in a CTE named user_orders
___ ___ ___ (
SELECT
___,
___ AS orders
FROM orders
GROUP BY ___)
SELECT
-- Write the conditions for the three buckets
CASE
WHEN ___ THEN 'Low-orders users'
WHEN ___ THEN 'Mid-orders users'
ELSE 'High-orders users'
END AS order_group,
-- Count the distinct users in each bucket
___ AS users
FROM user_orders
GROUP BY order_group;