Lesson 07 — Advanced SQL

Stack: PostgreSQL · Project: TicketFlow Status: Published — taught when you submit the previous one Prerequisite: Lesson 06 — Professional Git

Objectives

1. Write queries with CTEs and subqueries that are readable and composable.
2. Use window functions for rankings and running totals without collapsing rows.
3. Master joins and aggregations with judgement (and know when a JOIN duplicates).
4. Solve TicketFlow's three dominant queries in pure SQL.

1. CTEs: readability before cleverness

A CTE (WITH) names an intermediate step. Chain steps like a pipeline:

WITH active_reservations AS (
    SELECT user_id, COUNT(*) AS n
    FROM reservation
    WHERE status IN ('PENDING_PAYMENT', 'CONFIRMED')
    GROUP BY user_id
)
SELECT u.email, ra.n
FROM active_reservations ra
JOIN "user" u ON u.id = ra.user_id
WHERE ra.n >= 3
ORDER BY ra.n DESC;

Rule: if a query needs a comment explaining "what that subquery is", it is a CTE. In PostgreSQL 12+ non-recursive CTEs are inlined (same plan as the subquery), so readability costs you nothing.

2. Window functions: the ranking without GROUP BY

GROUP BY collapses rows; a window function adds a per-row calculation over a "window":

-- Top events by sales, with ranking and share of the total
SELECT e.title,
       SUM(ri.price_at_purchase)              AS sold,
       RANK()       OVER (ORDER BY SUM(ri.price_at_purchase) DESC) AS rank_,
       SUM(ri.price_at_purchase) / SUM(SUM(ri.price_at_purchase)) OVER () AS pct
FROM reservation_item ri
JOIN reservation r ON r.id = ri.reservation_id
JOIN event e       ON e.id = r.event_id
WHERE r.status = 'CONFIRMED'
GROUP BY e.title;

The ones you will use: ROW_NUMBER() (gapless pagination), RANK()/DENSE_RANK() (tops with ties), LAG()/LEAD() (difference from the previous period), SUM(...) OVER (PARTITION BY... ORDER BY...) (running totals).

3. Joins and aggregations: the silent duplication

JOINing a 1:N table and then SUMing counts wrong if you don't respect the level:

-- WRONG: the JOIN to items duplicates the payment rows → double counting
SELECT r.id, SUM(p.amount), SUM(ri.price_at_purchase)
FROM reservation r
JOIN payment p          ON p.reservation_id = r.id
JOIN reservation_item ri ON ri.reservation_id = r.id
GROUP BY r.id;

-- RIGHT: aggregate each relationship in its own CTE, then join the results
WITH payments AS (
  SELECT reservation_id, SUM(amount) AS total FROM payment GROUP BY reservation_id
),
items AS (
  SELECT reservation_id, SUM(price_at_purchase) AS total FROM reservation_item GROUP BY reservation_id
)
SELECT r.id, pg.total, it.total
FROM reservation r
JOIN payments pg ON pg.reservation_id = r.id
JOIN items it ON it.reservation_id = r.id;

JOIN types in one line: INNER (intersection), LEFT (everything from the left, NULL when there is no match — for "events with no sales"), EXISTS (only need to know if a match exists: cheaper than LEFT + IS NULL).

4. TicketFlow's three queries in SQL

-- 1. Availability of an event (seats with no active or confirmed reservation)
SELECT s.id
FROM seat s
WHERE s.event_id = %s
  AND NOT EXISTS (
    SELECT 1 FROM reservation_item ri
    JOIN reservation r ON r.id = ri.reservation_id
    WHERE ri.seat_id = s.id
      AND r.status IN ('PENDING_PAYMENT', 'CONFIRMED')
  )
ORDER BY s.sector, s.row, s.number;

-- 2. Revenue per day for the last 30 (complete series, including no-sale days)
SELECT d::date AS day, COALESCE(SUM(p.amount), 0) AS revenue
FROM generate_series(CURRENT_DATE - 29, CURRENT_DATE, interval '1 day') d
LEFT JOIN payment p ON p.created_at::date = d::date AND p.status = 'SUCCEEDED'
GROUP BY d ORDER BY d;

-- 3. Conversion rate per event (confirmed / created reservations)
SELECT e.title,
       COUNT(r.id) FILTER (WHERE r.status = 'CONFIRMED')::float
       / NULLIF(COUNT(r.id), 0) AS conversion
FROM event e LEFT JOIN reservation r ON r.event_id = e.id
GROUP BY e.title;

New tools in these queries: EXISTS/NOT EXISTS, generate_series (time series), FILTER (conditional aggregation), COALESCE/NULLIF (defaults and division by zero).

5. Recursive CTEs and the hop-aware inventory query

Trees (categories, folders, the venue/organization hierarchy) are NOT walked with N queries: they are walked with ONE recursive CTE. The TicketFlow case: the lesson 08 organization has sub-organizations; list every sale of a whole tree:

WITH RECURSIVE org_tree AS (
    -- base case: the root organization
    SELECT id, parent_id, name FROM org WHERE id = :root_id
    UNION ALL
    -- recursive step: the children of what was already visited
    SELECT o.id, o.parent_id, o.name
    FROM org o
    JOIN org_tree t ON o.parent_id = t.id
)
SELECT o.name, COUNT(r.id) AS sales, SUM(r.total_amount) AS raised
FROM org_tree t
JOIN org o ON o.id = t.id
LEFT JOIN reservations r ON r.org_id = t.id AND r.status = 'CONFIRMED'
GROUP BY o.name
ORDER BY raised DESC NULLS LAST;

Three production warnings: (1) the recursive one can LOOP FOREVER with corrupted data (a cycle in parent_id): add a guard (WHERE depth < 20 with a depth column incremented in the recursive step); (2) without an index on parent_id, every step is a seq scan (Lesson 09); (3) UNION ALL (fast, allows duplicate paths) vs UNION (deduplicates, more expensive): in honest trees, UNION ALL.

6. GROUP BY with judgement: the grain trap (revisited in depth)

The question that decides the design of EVERY report: what IS one row of the result? "Sales per day" (one row = day), "sales per event and day" (one row = event×day), "ranking of events per month with their best day" (one row = event, with a sub-aggregation inside). The wrong-grain mistake: mixing a day-level COUNT with a row-level MAX without the right GROUP BY → numbers that don't match lesson 08's accounting.

-- Per event: its total sales AND the day of its best sale (two grains in one query)
SELECT e.name,
       SUM(r.total_amount)                          AS event_total,
       (SELECT SUM(r2.total_amount)
          FROM reservations r2
         WHERE r2.event_id = e.id AND r2.status = 'CONFIRMED'
         GROUP BY DATE(r2.created_at)
         ORDER BY 1 DESC LIMIT 1)                   AS best_day
FROM events e
JOIN reservations r ON r.event_id = e.id AND r.status = 'CONFIRMED'
GROUP BY e.id, e.name;

The modern alternative without a subquery: GROUP BY GROUPING SETS ((e.id), (e.id, DATE(r.created_at))) — a single pass producing both the event grain AND the event×day grain, with GROUPING() to tell which level each row is. Useful for lesson 31's report; readable only with a comment.

Self-assessment (answer me in the chat)

1. When would you choose NOT EXISTS over LEFT JOIN... IS NULL, and why is the former usually clearer?
2. What is the difference between RANK() and ROW_NUMBER(), and what happens to ties in each?
3. Why does the "join + two SUMs" from point 3 duplicate amounts, and at which grain level must you aggregate?
4. What does FILTER (WHERE...) give you over CASE WHEN inside a SUM?
5. In the generate_series revenue query: why does the LEFT JOIN start from the series and not from payment?

Continue with the exercises. The solutions only after trying it yourself.