The Professor
Index Course index

Module 2 · Databases

Lesson 07 — Advanced SQL

Joins, subqueries, CTEs, window functions and aggregations on PostgreSQL.

Published
In this lesson
  1. Exercise 1 — CTE of repeat buyers
  2. Exercise 2 — Ranking with a window
  3. Exercise 3 — The silent duplication
  4. Exercise 4 — Availability
  5. Exercise 5 — Series and conversion
  6. Exercise 6 — The tree
  7. Exercise 7 — The two grains
  8. Professor's summary

Exercise 1 — CTE of repeat buyers

sql
WITH confirmed AS (
    SELECT user_id, COUNT(*) AS n
    FROM reservation WHERE status = 'CONFIRMED' GROUP BY user_id
),
spending AS (
    SELECT r.user_id, SUM(p.amount) AS total
    FROM reservation r JOIN payment p ON p.reservation_id = r.id
    WHERE r.status = 'CONFIRMED' AND p.status = 'SUCCEEDED'
    GROUP BY r.user_id
)
SELECT u.email, c.n, g.total
FROM confirmed c
JOIN "user" u ON u.id = c.user_id
JOIN spending g  ON g.user_id = c.user_id
WHERE c.n >= 3
ORDER BY c.n DESC;
  1. Without WITH it is the same query with nested subqueries: it works, but your eye must jump inside-out to understand. The CTE wins in review. (The HAVING version is also valid for the n>=3 filter.)

Exercise 2 — Ranking with a window

  1. RANK() leaves gaps with ties (1,1,3); DENSE_RANK() doesn't (1,1,2). For "top sales" it doesn't matter; for "position in a paginated list" use ROW_NUMBER().
  2. LAG(SUM(...)) OVER (ORDER BY e.starts_at) and the difference against the current value; on the first event LAG gives NULL → wrap it with COALESCE(..., 0).
  3. The top-N-per-group pattern:
sql
SELECT * FROM (
  SELECT r.*, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY created_at) AS rn
  FROM reservation r
) t WHERE rn <= 2;

Exercise 3 — The silent duplication

  1. With 1 reservation, 2 items and 1 payment: the JOIN produces 2 rows (each item crosses with the payment), so SUM(p.amount) = 2× the payment. It is the grain error: you aggregate at reservation level but the JOIN exploded to item level.
  2. The version with payments/items CTEs aggregates each relationship at reservation_id grain before joining: correct amounts. Moral: aggregate first, cross later.

Exercise 4 — Availability

  1. They must match (same semantics as 00b's subquery). If not, check the filtered statuses.
  2. LEFT JOIN reservation_item ri... LEFT JOIN reservation r... WHERE r.id IS NULL: valid and sometimes faster; NOT EXISTS expresses the intent ("no active reservation exists") and is what I would recommend in review.
  3. ORDER BY s.sector, s.row, s.number LIMIT 50 — pagination in the DB, not in Python.

Exercise 5 — Series and conversion

  1. With yesterday's payment, the day appears with an amount; without it, 0.00. The trick is that the series is the base table and the LEFT JOIN only decorates: empty days don't disappear.
  2. Expiration: COUNT() FILTER (WHERE r.status = 'CANCELLED' AND r.expires_at < now())::float / NULLIF(COUNT(), 0) over reservations; for semantic precision distinguish user-cancelled from timed-out (lesson 29's job will mark the cause).

Exercise 6 — The tree

  1. The CTE with the guard:
sql
WITH RECURSIVE org_tree AS (
    SELECT id, parent_id, name, 0 AS depth FROM org WHERE id = :root_id
    UNION ALL
    SELECT o.id, o.parent_id, o.name, t.depth + 1
    FROM org o JOIN org_tree t ON o.parent_id = t.id
    WHERE t.depth < 20                 -- the anti-cycle guard
)
...
  1. The A→B→A cycle without the guard: the recursive step re-finds A indefinitely until Postgres aborts ("statement recursion depth exceeded" on newer versions, or memory/timeout on older ones: the disaster is real, not theoretical). With the guard: it ends at depth 20 with repeated rows from the cycle — the corrupt data needs a fix, the query only contained the damage.
  2. Without an index on parent_id: each hop = a seq scan of org (a 10k-org tree: 10k rows read × 3 hops). With the index: an index scan per hop (~3-5 rows). Total cost drops from tens of ms to µs: Lesson 09 applied to the recursive CTE.

Exercise 7 — The two grains

  1. The GROUPING SETS:
sql
SELECT e.name,
       DATE(r.created_at)                        AS day,
       SUM(r.total_amount)                       AS total,
       GROUPING(e.id, DATE(r.created_at))        AS level  -- 0=event×day row, 1=event row
FROM events e JOIN reservations r ON r.event_id = e.id AND r.status='CONFIRMED'
GROUP BY GROUPING SETS ((e.id), (e.id, DATE(r.created_at)));

The cross-check: an event-level row = the SUM of its event×day rows (the 08 test's assert). If it doesn't square: there is data with NULL dates (GROUPING groups it separately) — the typical finding.

  1. The plan: GROUPING SETS = ONE aggregation pass (faster on large datasets: one read); the subquery = 2 passes but the plan can be simpler with selective per-event indexes. With 10k events × 90 days: GROUPING SETS wins ~2×; for queries aimed at ONE event, the subquery suffices. The rule: full report → sets; targeted detail → subquery.
  2. Each report's "grain": the home page's sales = one row per EVENT; lesson 31's dashboard = one row per event×day; the ledger = one row per TRANSACTION (the finest, the source of all). Three grains, one accounting truth: the check is that the finer ones sum into the coarser ones.

Professor's summary

  • CTEs for readability; aggregate at the right grain before crossing; window functions for rankings/series without collapsing rows.
  • NOT EXISTS expresses intent; generate_series + LEFT JOIN for complete series; FILTER for conditional metrics.
  • SQL is a reading skill as much as a writing one: a query in review is appreciated like code.

After the correction: Lesson 08 — Data modeling.

^ top