Lesson Description
Build multi-stage analysis pipelines with chained CTEs to handle complex business logic cleanly. You will learn how to validate each stage and produce a final result that is easy to maintain.
Lesson 04
Lesson Description
Build multi-stage analysis pipelines with chained CTEs to handle complex business logic cleanly. You will learn how to validate each stage and produce a final result that is easy to maintain.
Easy Project
Mini project: Build a 3-step CTE pipeline that isolates top customers, calculates spend metrics, and returns a clean final view.
In the CTEs lesson you wrote one named step and then a main query. Real reporting questions often need three or four steps, each building on the last. Take this one from the store database: "How many of our customers are one-time buyers, and how many come back?" To answer it you need to count delivered orders per customer, then label each customer with a tier based on that count, and then count customers per tier. That's three distinct steps, and each one needs the result of the step before.
Chained CTEs let you write that as a pipeline. You list several CTEs in a single WITH clause, separated by commas, and each CTE can read from any CTE defined above it. The final SELECT reads from whichever step it needs. The result is a query you can read like a recipe: first this, then this, then this.
WITH
step_one AS (
SELECT ...
FROM real_table
),
step_two AS (
SELECT ...
FROM step_one
),
step_three AS (
SELECT ...
FROM step_two
JOIN another_table ON ...
)
SELECT ...
FROM step_three;
The rules:
WITH keyword at the top, never repeated.Here is the customer tiers question as three steps:
WITH
customer_orders AS (
SELECT customer_id, COUNT(*) AS delivered_orders
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
),
customer_tiers AS (
SELECT customer_id,
delivered_orders,
CASE
WHEN delivered_orders >= 3 THEN 'loyal'
WHEN delivered_orders = 2 THEN 'returning'
ELSE 'one-time'
END AS tier
FROM customer_orders
)
SELECT tier,
COUNT(*) AS customers,
SUM(delivered_orders) AS orders
FROM customer_tiers
GROUP BY tier
ORDER BY customers DESC;
The result has 2 rows: 13 returning customers who account for 26 delivered orders, and 7 one-time customers with 7 orders. That covers all 33 delivered orders. Notice what's missing: there's no loyal row, because no customer has three or more delivered orders. GROUP BY only produces groups that have rows. If your report must always show all three tiers, you'd need a list of tiers to LEFT JOIN from, which is a good exercise in its own right.
Splitting the work this way makes each step easy to check. The CASE logic lives in one place, separate from the counting and the summary.
In the hospital database, "Which doctors carry an above-average appointment load?" You need each doctor's count, and then the average of those counts:
WITH
doctor_load AS (
SELECT doctor_id, COUNT(*) AS appointments
FROM appointments
WHERE status <> 'cancelled'
GROUP BY doctor_id
),
avg_load AS (
SELECT AVG(appointments) AS avg_appts
FROM doctor_load
)
SELECT d.first_name, d.last_name, d.specialty,
dl.appointments,
ROUND(al.avg_appts, 1) AS avg_appts
FROM doctor_load dl
JOIN doctors d ON d.id = dl.doctor_id
CROSS JOIN avg_load al
WHERE dl.appointments > al.avg_appts
ORDER BY dl.appointments DESC, d.last_name;
The average is 4.6 appointments per doctor, and 5 doctors are above it: Sarah Johnson, Aisha Khan, Priya Patel and Yuki Tanaka with 8 each, and Olivia Bennett with 7. avg_load has exactly one row, so the CROSS JOIN simply attaches that one number to every doctor row, which makes it available in both WHERE and SELECT.
There's a subtle choice baked into this query. doctor_load only has rows for doctors with at least one non-cancelled appointment, which is 14 of the 15 doctors. Marcus Wright, who has none, isn't part of the average. If you wanted him counted as a zero, you'd start doctor_load from doctors with a LEFT JOIN to appointments, and the average would drop. Neither version is wrong, but you should choose deliberately.
Chained CTEs pair naturally with window functions, which you'll study in depth in the next course. Here's a month-over-month revenue report for the store, excluding cancelled orders:
WITH
monthly_revenue AS (
SELECT DATE_TRUNC('month', o.order_date)::date AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY 1
),
with_prev AS (
SELECT month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM monthly_revenue
)
SELECT month,
revenue,
prev_revenue,
revenue - prev_revenue AS change,
ROUND(100.0 * (revenue - prev_revenue) / prev_revenue, 1) AS pct_change
FROM with_prev
ORDER BY month;
This returns 5 rows, January to May 2025. January has 918.72 in revenue and NULL for everything comparative, because there's no earlier month. February drops 17.5% to 758.39, March more than doubles to 1,523.86 (up 100.9%), April edges up 6.1% to 1,616.74, and May falls 19.8% to 1,296.51. The with_prev step exists so the final SELECT can refer to prev_revenue by name three times, instead of repeating LAG(revenue) OVER (ORDER BY month) in every expression.
"For each job title, where do employees sit within the official salary band?" The jobs table holds min_salary and max_salary, so the first step computes a position for each employee (0% at the bottom of the band, 100% at the top), and the second summarises by title:
WITH
emp_band AS (
SELECT j.title,
ROUND(100.0 * (e.salary - j.min_salary)
/ NULLIF(j.max_salary - j.min_salary, 0), 1) AS band_pct
FROM employees e
JOIN jobs j ON j.id = e.job_id
),
band_summary AS (
SELECT title,
COUNT(*) AS employees,
ROUND(AVG(band_pct), 1) AS avg_band_pct,
MAX(band_pct) AS max_band_pct
FROM emp_band
GROUP BY title
)
SELECT *
FROM band_summary
ORDER BY avg_band_pct DESC NULLS LAST
LIMIT 5;
Accountants come out on top: 5 employees averaging 77.5% of the way up their band, with at least one at exactly 100%. Next are Marketing Manager (66.7%), Sales Representative (52.1% across 4 people), HR Representative (50.0%) and Finance Manager (48.7%). NULLIF guards against a band where minimum and maximum are equal, which would otherwise be a division by zero.
Chaining doesn't mean PostgreSQL runs each step to completion and stores it. As you saw in the previous lesson, a CTE referenced once is normally inlined, so the planner sees the whole chain as one big query and optimises it as a unit. A CTE referenced from two places (like doctor_load in example 2, which is read by avg_load and by the main query) is materialized once and then read twice, so you don't pay for the aggregation two times.
Because the planner sees the whole chain, splitting a query into five readable CTEs costs nothing compared with one dense query that does the same work. Optimise for the person who reads it next.
The most practical benefit of chaining is that you can inspect any step. Temporarily replace the main query with SELECT * FROM customer_orders; (or any other CTE name) and run it. The later CTEs are simply ignored when nothing references them. Once that step looks right, restore the real final query.
WITH
top_depts AS (
SELECT * FROM dept_stats WHERE avg_sal > 9000
),
dept_stats AS (
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
)
SELECT * FROM top_depts;
PostgreSQL rejects this with relation "dept_stats" does not exist, and helpfully adds There is a WITH item named "dept_stats", but it cannot be referenced from this part of the query. The fix is to put dept_stats first. Order your CTEs the way you'd explain the logic out loud.
WITH a AS (SELECT 1 AS x),
WITH b AS (SELECT 2 AS y)
SELECT * FROM a, b;
This is a syntax error at or near "WITH". There is one WITH per statement; the second CTE follows a comma only: WITH a AS (...), b AS (...).
A frequent reason to chain is ranking. This looks reasonable but fails:
SELECT department_id,
AVG(salary) AS avg_salary,
RANK() OVER (ORDER BY AVG(salary) DESC) AS rnk
FROM employees
GROUP BY department_id
HAVING RANK() OVER (ORDER BY AVG(salary) DESC) <= 3;
PostgreSQL reports window functions are not allowed in HAVING (and the same for WHERE), because window functions are calculated after filtering. The fix is exactly what this lesson teaches: compute the rank in one CTE, then filter on rnk in the next step or in the main query.
Multiple CTEs in one WITH clause work the same way in SQL Server, SQLite and MySQL 8.0 or later, including the rule that a CTE can only see the ones above it. None of them let you repeat the WITH keyword within a statement.
The exercises below ask you to:
Build each chain one step at a time, running SELECT * FROM the newest CTE before you add the next.
WITH clause, separated by commas, with no comma before the main query.Several CTEs can build on each other, each adding one transformation: clean the data, aggregate it, rank it, then pick the answer. This is how analysts write long queries that stay reviewable, because every step has a name and can be checked on its own.
Top customer per country, step by step
WITH spend AS (
SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.customer_id
),
ranked AS (
SELECT c.country, c.first_name, c.last_name, s.total,
RANK() OVER (PARTITION BY c.country ORDER BY s.total DESC) AS rnk
FROM spend s
JOIN customers c ON c.id = s.customer_id
)
SELECT country, first_name, last_name, total
FROM ranked
WHERE rnk = 1
ORDER BY total DESC;spend aggregates, ranked orders customers within each country, and the final query keeps the winners.
Debugging one step
WITH spend AS (
SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.customer_id
)
SELECT * FROM spend ORDER BY total DESC LIMIT 5;Replacing the final SELECT with a look at one CTE is the fastest way to check an intermediate result.
Window functions are computed after WHERE, so the rank does not exist yet when WHERE runs. Filter in the next step.
Concept check
3 quick questions. One at a time. Instant score at the end.
Write two CTEs: dept_stats (avg salary and headcount per department) and ranked_depts (rank departments by avg salary descending). Return department name, avg_salary, and emp_count for all departments, ordered by rank.
No results yet
Run a query to see results here
Use two CTEs: category_revenue (total revenue per category from order_items + products) and ranked_cats (rank by revenue descending). Return the top 3 category name and revenue, ordered by rank.
No results yet
Run a query to see results here
Use two CTEs: doc_stats (appointment count per doctor) and ranked_docs (rank doctors by appointment count descending). Return the top 5 doctor first_name, last_name, and total_appointments, ordered by rank.
No results yet
Run a query to see results here
Final checkpoint
Pass mark: 80%. Review each explanation after you submit.
1. Which clause should you use to filter grouped results?
2. What is the safest way to avoid accidental row multiplication in joins?
3. Which feature is best for ranking rows within partitions?
4. Why should final analytical queries usually include ORDER BY?
5. When query logic gets complex, what improves readability most?