Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Basic multi-level aggregation
Multi-level aggregation in groups
Multi-level aggregation with custom classification
Three or more aggregation levels
14. Three aggregation levels – example 2


Good job! Now, let's take a look at a slightly different example.

Say we want to count the number of orders processed by each employee, and we only want to show the employees whose order_count is greater than the average order_count for all employees. Have a look:

WITH order_count_employees AS (
    COUNT(order_id) AS order_count
  FROM orders
  GROUP BY employee_id

avg_order_count AS (
    AVG(order_count) AS avg_order_count
  FROM order_count_employees

FROM order_count_employees,
WHERE order_count > avg_order_count;

In the first CTE, we count the number of orders processed by each employee. In the second CTE, we find the average order count based on the first CTE. But look what happens in the outer query: We simply provide both CTE names in the FROM clause, separated with a comma. Thanks to this, we can refer to columns from both CTEs in the WHERE clause and show all columns in the SELECT clause. As a result, each employee with an above average order count will be shown with their order count. We'll also show the average count for reference:

employee_id order_count avg_order_count
1 123 92
2 96 92
3 127 92
4 156 92
8 104 92


Among orders shipped to Italy, show all orders that had an above-average total value (before discount). Show the order_id, order_value, and avg_order_value column. The avg_order_value column should show the same average order value for all rows.

Stuck? Here's a hint!

Use the following template:

WITH order_values AS (
avg_order_value AS (
FROM order_values,
WHERE ...;