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