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 as the database writes it
  5. Exercise 5 — Revenue series and conversion
  6. Exercise 6 — The organization tree (recursive CTE)
  7. Exercise 7 — The two-grain report
  8. Submit

On your TicketFlow PostgreSQL (psql or Django shell with connection.cursor()). Do not look at solutions.md before submitting.

Setup: minimal test data in the Django shell (or what you already have from 00b).

Exercise 1 — CTE of repeat buyers

  1. With a CTE, list users with 3 or more confirmed historical reservations and their count.
  2. Rewrite it with a subquery in the FROM without WITH: which version reads better in a review?
  3. Add the total amount spent by each one (second CTE + JOIN).

Exercise 2 — Ranking with a window

  1. Ranking of events by revenue (RANK()). What happens if two tie?
  2. Add a column with the previous event's revenue (LAG()) and the difference.
  3. ROW_NUMBER() over (event_id, created_at) of reservations: number each event's reservations by age and keep the first 2 per event (the top-N-per-group pattern).

Exercise 3 — The silent duplication

  1. Write the "WRONG" query from lesson point 3 on your DB (or simulate it with little data) and demonstrate with a case (1 reservation, 2 items, 1 payment) that SUM(p.amount) comes out multiplied.
  2. Fix it with separate CTEs and check the correct amount.

Exercise 4 — Availability as the database writes it

  1. Run the availability query (lesson point 4.1) and compare the result with your Python available_seats() from 00b. Do they match?
  2. Rewrite it with LEFT JOIN + IS NULL instead of NOT EXISTS. Which one reads clearer to you?
  3. Add the sector and the ordering by row/number, and limit to the first 50 (SQL pagination).

Exercise 5 — Revenue series and conversion

  1. Run the 30-day revenue query with generate_series and manually insert a payment "from yesterday" to see the day with a non-zero value.
  2. Conversion query per event with FILTER. Add the "expiration rate" one (expired reservations / created).

Exercise 6 — The organization tree (recursive CTE)

  1. Write the recursive CTE of the organization tree (§5) with the anti-cycle guard (depth column, WHERE depth < 20). Verify: a 3-level tree returns sales for every node, root included.
  2. Break the data: insert a cycle (A → B → A). Run WITHOUT the guard (does the query hang or explode?) and with the guard (does it finish?). Document the evidence of the disaster and of the remedy.
  3. The recursive step's index: EXPLAIN the query without and with an index on parent_id (Lesson 09). What does each hop cost?

Exercise 7 — The two-grain report

  1. Rewrite the sales-per-event report with GROUPING SETS ((event), (event, day)) and GROUPING() to label each row with its level. Verify that the event-grain total matches the sum of the event×day grain (the accounting cross-check from 08).
  2. The equivalent naïve query with a subquery (§6's): compare plans (EXPLAIN ANALYZE) and times. When does each pay off?
  3. Document in 5 lines what each report of your project IS per row (its "grain") — the question this module leaves installed.

Submit

Paste the queries and trimmed outputs. When corrected we close Lesson 07; next up Lesson 08 — Data modeling.

^ top