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 →