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 ...;
