DT8: Advanced SQL for analysis
Readable queries with CTEs
🎯You'll be able to
Break a query into named steps with common table expressions (CTEs), check each step, and write SQL others can review.
🏠The everyday picture
A good recipe lists steps: soak, grind, ferment, cook. A recipe written as one long sentence might be right, but nobody can check it or find the step that went wrong.
📘What it really is
- A CTE names a step with
with name as (select ...). Later steps read from earlier ones, top to bottom:
sql
with delivered as (
select * from marts.fct_order_lines where status = 'delivered'
),
by_store_product as (
select store, product_key, sum(net_amount_paise) as rev
from delivered group by store, product_key
)
select * from by_store_product order by rev desc;
- One idea per step: filter, join, aggregate, rank. Name steps for what they hold (
delivered,by_store_product), nott1,t2. -
Check each step by selecting from it with a count: does
deliveredhave 8,765 rows? Does the next step have one row per store and product? -
Formatting habits: one column per line in long selects, keywords in a consistent case, comments for business rules (
-- refunds are excluded from net revenue). -
Modern engines treat CTEs as part of one query plan, so naming steps rarely costs speed.
📝A worked example
Input: run the query above against the solution's warehouse, then check the first step with select count(*) from delivered inside the
same with.
Expected output: 8,765 delivered lines, then one row per store and product, led by Sugar 1 kg (SKU-138) at Hadapsar. Breaking the query
into steps made each count easy to check.
🌍Where it's used
- dbt models: most are a chain of CTEs (import, logic, final).
- Code review: reviewers read and check one step at a time.
- Analyst notebooks: queries that colleagues can reuse months later.
📝Case scenario
An analyst's 200-line query for quarterly churn was copied into five reports. A reviewer asked where cancelled subscriptions were removed; nobody could tell. Rewritten as six named CTEs, the review found cancelled subscriptions were removed twice, inflating churn.
🖱️Try it
Rewrite this as CTEs: "net revenue per store per weekday, only for Saturdays and Sundays", joining fct_order_lines to dim_date.
Success: three steps (filter, join, aggregate), each one you can count.
⚠️Common mistakes
- Nested subqueries five deep. Sign: nobody can explain the query. Fix: CTEs, top to bottom.
- Names like
t1. Sign: you have to read the SQL to know what a step holds. Fix: name what it contains. - Not checking steps. Sign: the final number is wrong and you don't know which step. Fix: count each step's rows.
🛠️For your project
Project 3's dashboard queries read the marts; writing your own checks of its numbers in CTEs makes them easy to review.
✅Quick check
What does a CTE do?
Show the answer
Gives a step of a query a name, so later steps can read from it.
How do you check a long query?
Show the answer
Count the rows of each step and compare with what you expect.