Cheat sheets · Module 5
CTEs & Reshaping Data cheat sheet
Write multi-step queries that stay readable.
CTE · MIN · date math
A CTE (WITH name AS (...)) names a subquery so you can build a query in readable steps. A typical pattern: step 1 computes something per customer, step 2 joins it back to the customers table.
WITH first_post AS ( SELECT author_id, MIN(posted_at) AS first_at FROM posts GROUP BY author_id ) SELECT a.name, f.first_at FROM authors a JOIN first_post f ON f.author_id = a.id;Practice: Days to first purchase →
to_char · GROUP BY month
To aggregate by month, turn each date into a month label and group by it. to_char(d, 'YYYY-MM') gives text like '2024-03'; date_trunc('month', d) gives a timestamp at the start of the month.
SELECT to_char(created_at, 'YYYY-MM') AS month, COUNT(*) AS signups FROM accounts GROUP BY 1 ORDER BY 1;Practice: Monthly spend →
Pivot with FILTER / CASE
To pivot rows into columns, group by the row key and compute one aggregate per column, each restricted to one value: SUM(x) FILTER (WHERE category = 'a') in Postgres, or the portable SUM(CASE WHEN category = 'a' THEN x END).
SELECT store, COUNT(*) FILTER (WHERE status = 'open') AS open, COUNT(*) FILTER (WHERE status = 'closed') AS closed FROM tickets GROUP BY store;Practice: Spend by category, one row per account →
Conditional aggregation · FILTER
Conditional aggregation counts only some rows inside a group: COUNT(*) FILTER (WHERE status = 'x') in Postgres, or the portable SUM(CASE WHEN status = 'x' THEN 1 ELSE 0 END). Watch out for integer division: COUNT returns an integer, so 1 / 3 is 0. Multiply by 100.0 first.
SELECT store_id, ROUND(100.0 * COUNT(*) FILTER (WHERE late) / COUNT(*), 2) AS late_pct FROM deliveries GROUP BY store_id;Practice: Refund rate per customer →
JOIN + date difference
Join conditions can filter too: JOIN events p ON p.user_id = u.id AND p.event = 'purchase' picks only purchase rows. Subtracting two DATEs gives whole days, so 'within N days' is date_b - date_a <= N.
SELECT a.name, t.closed_on - t.opened_on AS days_open FROM tickets t JOIN agents a ON a.id = t.agent_id AND t.priority = 'high';Practice: Fast converters →
CTE + LAG · growth rate
Growth questions need last period's value next to this period's: aggregate per month in a CTE, then LAG(total) OVER (ORDER BY month) gives the previous month. Growth % = 100 * (this - previous) / previous. The first month has no previous value, so it's NULL.
WITH w AS ( SELECT week, SUM(visits) AS visits FROM traffic GROUP BY week ) SELECT week, visits, visits - LAG(visits) OVER (ORDER BY week) AS change FROM w;Practice: Month-over-month growth →
COUNT(DISTINCT month) · relational division
'Did X in every period' is relational division. The usual SQL trick: filter to the period, group by the entity, and keep groups where the number of distinct periods equals the number of periods: HAVING COUNT(DISTINCT date_trunc('month', d)) = 3. Count distinct months, not rows, because one busy month can fake the count.
SELECT member_id
FROM gym_visits
WHERE visit_date >= '2024-01-01' AND visit_date < '2024-07-01'
GROUP BY member_id
HAVING COUNT(DISTINCT date_trunc('month', visit_date)) = 6;Practice: Active in every month →PERCENTILE_CONT · ordered-set aggregates
AVG gives the mean, which one unusually high or low salary can drag up or down. The median is the middle value: in Postgres, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x). With an even count it averages the two middle values.
SELECT city, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_price FROM listings GROUP BY city;Practice: Median salary by department →
WITH RECURSIVE · hierarchies
WITH RECURSIVE walks a hierarchy of unknown depth. It has two parts joined by UNION ALL: an anchor query for the starting rows (the top of the tree), and a recursive query that joins the table to the rows found so far to get the next level. It repeats until a step finds no new rows.
WITH RECURSIVE tree AS ( SELECT id, name, 1 AS depth FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, t.depth + 1 FROM folders f JOIN tree t ON f.parent_id = t.id ) SELECT * FROM tree;Practice: Org chart with recursive CTE →