Instruction
Perfect! We can also combine multi-level aggregation with custom classifications. Suppose we want to find the number of customers divided into three groups: those with fewer than 10 orders, those with 10–20 orders, and those with more than 20 orders. Here's a query that can do just that:
WITH customer_order_counts AS (
SELECT
customer_id,
CASE
WHEN COUNT(o.order_id) > 20
THEN 'more than 20'
WHEN COUNT(o.order_id) <= 20 AND COUNT(o.order_id) >= 10
THEN 'between 10 and 20'
ELSE 'less than 10'
END AS order_count_cat
FROM orders o
GROUP BY customer_id
)
SELECT
order_count_cat,
COUNT(customer_id) AS customer_count
FROM customer_order_counts
GROUP BY order_count_cat;
In the inner query, we used the CASE WHEN construction to classify each customer into one of three groups. The customer’s group is shown in the order_count_cat column. Next, we used the order_count_cat column in the outer query with the COUNT() function to show the number of customers in each group.
Exercise
Count the number of high value and low value customers. If the total price paid by a given customer for all their orders is more than $20,000 before discounts, treat the customer as 'high-value'. Otherwise, treat them as 'low-value'.
Create a report with two columns: category (either 'high-value' or 'low-value') and customer_count.
Stuck? Here's a hint!
Use the following CASE WHEN construction in the inner query:
CASE
WHEN SUM(quantity * unit_price) > 20000
THEN 'high-value'
ELSE 'low-value'
END AS category
