Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Basic multi-level aggregation
Multi-level aggregation in groups
Multi-level aggregation with custom classification
11. Multi-level aggregation with CASE WHEN – theory
Three or more aggregation levels
Summary

## 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