A report in steps: WITH
In short: WITH (a common table expression, CTE) names each step of a query so it reads top to bottom and one step can be used more than once.
Problem
A report from the inside out
The owner wants a list of the customers who have spent over 100 € (without the cancelled orders), with their number of orders. With subqueries in FROM you can already do it:
The result is correct. But try to say what the query does: you have to start from the innermost brackets and work outwards. In a month you will not recognise it yourself.
The idea
WITH
WITH gives a step a name, before the main query:
order_totals is the value of every order that is not cancelled: 53 rows. After WITH, the name is used like a table. Such a named step is called a CTE (common table expression).
There can be several steps, separated by commas. Each one sees the steps before it:
The same report as at the start, but it reads from the top down: the value of every order, then a total for every customer, then only those over 100 €. Seven customers come out, led by Maria (375.70 € in 12 orders).
Try it
Orders above average
Show the number and value of the orders (without the cancelled ones) whose value is above the average order value. The field already has the order_totals step.
Solution
WITH order_totals AS (
SELECT o.id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY o.id, o.customer_id
)
SELECT id, ROUND(total, 2) AS total
FROM order_totals
WHERE total > (SELECT AVG(total) FROM order_totals);WHERE total > (SELECT AVG(total) FROM order_totals). 22 orders come out. Here you see one more advantage: order_totals is used twice, once in FROM and once in the subquery. With a subquery in FROM you would have written it twice.
See it
How long the name lives
A name from WITH holds only for the query it is part of. Here there are two queries: the first counts the orders, the second asks for order_totals again:
WITH does not create a table in the database. If you need a step in many reports, there are views (VIEW) for that, which we will meet in the module on the schema.
The idea
When WITH, when a subquery
The result is the same. The difference is for the person reading:
- a short condition such as
price > (SELECT AVG(price) FROM products)is clearest in place, as a subquery; - a report of several steps is clearer with
WITH: every step has a name that says what it is; - a step you need twice is written once.
It also helps when hunting for a mistake: replace the last SELECT with SELECT * FROM order_totals and you see what each step gives.
More precisely, if you are curious
Whether the database runs every step separately or merges them into one query is up to the database. In PostgreSQL before version 12 every WITH was run separately, and from version 12 on the database can merge it into the main query. If a WITH query is ever slower than the same one with subqueries, the reason is often there.
Exercise
Three tasks
1. The sales team wants the revenue by city, to decide where to advertise.
Show the city and the revenue from the orders of its customers (without the cancelled ones), rounded to two decimal places. Start from the order_totals step.
Solution
WITH order_totals AS (
SELECT o.id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY o.id, o.customer_id
)
SELECT c.city, ROUND(SUM(ot.total), 2) AS revenue
FROM order_totals ot
JOIN customers c ON c.id = ot.customer_id
GROUP BY c.city;JOIN customers to order_totals and GROUP BY c.city. Seven rows: Sofia leads with 658.70 €. Boris has no city, so one row has NULL for the city (66.60 €). Do not lose it: that is real money.
2. Which categories have brought in over 200 € (without the cancelled orders)?
Show the category name and its revenue (the sum of quantity times unit price), only for the categories with revenue over 200 €. Make one step with the revenue by category_id and then join it to categories.
Solution
WITH category_sales AS (
SELECT p.category_id, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY p.category_id
)
SELECT c.name, ROUND(cs.revenue, 2) AS revenue
FROM category_sales cs
JOIN categories c ON c.id = cs.category_id
WHERE cs.revenue > 200;The step joins order_items with orders (for the status) and products (for the category), and the main query adds the name and filters. Three categories: “Kitchen” (516.90 €), “Dairy” (315.50 €) and “Drinks” (231.20 €).
3. In the module on joins you made the grid of every city and every subcategory with CROSS JOIN. Now fill it in: the revenue for every pair, with 0 where there are no sales.
Show the city, the subcategory name (column category) and the revenue (column revenue) from the customers of that city in that subcategory (without the cancelled orders), rounded to two decimal places. For pairs without sales the revenue is 0. The field already has the grid.
Solution
WITH grid AS (
SELECT DISTINCT cu.city, cat.id AS category_id, cat.name AS category
FROM customers cu
CROSS JOIN categories cat
WHERE cu.city IS NOT NULL AND cat.parent_id IS NOT NULL
),
sales AS (
SELECT cu.city, p.category_id, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN customers cu ON cu.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY cu.city, p.category_id
)
SELECT g.city, g.category, COALESCE(ROUND(s.revenue, 2), 0) AS revenue
FROM grid g
LEFT JOIN sales s ON s.city = g.city AND s.category_id = g.category_id;Two steps: grid is the grid, sales is the revenue by city and category_id. Then grid LEFT JOIN sales on both columns and COALESCE(…, 0). 36 rows, four of them with zero: Burgas in “Kitchen”, “Dairy” and “Drinks”, and Ruse in “Cleaning”. The grid is on the left, so no pair gets lost.
Test