Get startedGet started for free

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

View Course

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;
Edit and Run Code