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 (
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
COUNT(customer_id) AS customer_count
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.