Summer Deals - 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
Three or more aggregation levels
14. Three aggregation levels – example 2
Summary

Instruction

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 (
  SELECT
    employee_id,
    COUNT(order_id) AS order_count
  FROM orders
  GROUP BY employee_id
),

avg_order_count AS (
  SELECT
    AVG(order_count) AS avg_order_count
  FROM order_count_employees
)

SELECT
  employee_id,
  order_count,
  avg_order_count
FROM order_count_employees,
  avg_order_count
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

Exercise

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 (
  ...
)
SELECT
  ...
FROM order_values,
  avg_order_value
WHERE ...;