Week 7: Subqueries, CTEs & Window Functions

1. Subqueries: A Query Inside a Query

A subquery is a complete SELECT nested inside another query — used wherever a condition or a value needs to be computed from the data itself, rather than a fixed number typed into the query by hand:

-- Orders priced above the OVERALL average -- the average has to be computed
-- from the same table, so it can't just be typed in as a literal number
SELECT order_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

-- A subquery can also stand in for a table, in the FROM clause
SELECT category, order_count
FROM (
  SELECT category, COUNT(*) AS order_count
  FROM orders
  GROUP BY category
) AS category_counts
WHERE order_count > 2;

The first example's subquery runs once, produces a single number (the average), and that number is substituted into the WHERE clause of the outer query — this shape is called a scalar subquery. The second wraps an entire grouped query in parentheses and treats its result as if it were a regular table, giving it an alias (category_counts) so the outer query can reference it. Every subquery needs that alias when used in FROM — SQL won't accept an unnamed derived table.

2. Chaining CTEs

Week 6 introduced a single WITH CTE as a readability tool. CTEs can also chain — a later CTE can reference an earlier one directly, turning a multi-step analysis into a sequence of clearly labeled stages instead of subqueries nested inside subqueries inside subqueries:

WITH monthly_sales AS (
  -- Step 1: collapse every order down to one total per month
  -- (DATE_TRUNC is PostgreSQL; MySQL's equivalent is DATE_FORMAT(order_date, '%Y-%m-01'))
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total_amount
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
),
ranked_months AS (
  -- Step 2: rank those monthly totals, using the CTE from Step 1 as a normal table
  SELECT month, total_amount,
         RANK() OVER (ORDER BY total_amount DESC) AS sales_rank
  FROM monthly_sales
)
-- Step 3: the final query just filters the already-ranked result
SELECT * FROM ranked_months WHERE sales_rank <= 2;

Each AS (...) block is a named, self-contained step — ranked_months reads from monthly_sales exactly as if it were a real table, even though neither one is ever actually written to the database. This is the single biggest structural upgrade for a query past a certain complexity: instead of one dense block doing everything at once, the logic reads top to bottom as a sequence of named stages, each easy to test on its own by temporarily changing the final SELECT to read from an earlier stage.

3. ROW_NUMBER & RANK

Both assign a position to each row based on an ordering — the difference is entirely about how they handle ties:

SELECT month, total_amount,
       ROW_NUMBER() OVER (ORDER BY total_amount DESC) AS row_num,
       RANK()       OVER (ORDER BY total_amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY total_amount DESC) AS dense_rnk
FROM monthly_sales;

-- If two months tie for the highest total_amount:
-- row_num:    1, 2, 3, 4   -- always unique, ties broken arbitrarily
-- rnk:        1, 1, 3, 4   -- ties share a rank, then SKIPS to 3 (2 is never used)
-- dense_rnk:  1, 1, 2, 3   -- ties share a rank, next rank is NOT skipped

OVER (ORDER BY total_amount DESC) is what makes these window functions rather than regular aggregates — instead of collapsing rows together like GROUP BY does, a window function calculates a value per row while still being aware of the other rows around it (here, "everything ordered by total, descending"). ROW_NUMBER is the right choice whenever a genuinely unique position is needed regardless of ties (like picking exactly one "top" row); RANK or DENSE_RANK is right whenever ties should be reported as tied.

4. Running Totals With OVER()

The same OVER() syntax turns a normal aggregate into a running one — recomputed at each row instead of once for the whole result:

SELECT month, total_amount,
       SUM(total_amount) OVER (ORDER BY month) AS running_total
FROM monthly_sales;

-- month     total_amount   running_total
-- 2026-01   2390           2390
-- 2026-02   3150           5540   -- 2390 + 3150
-- 2026-03   1720           7260   -- 5540 + 1720

Without OVER(), SUM(total_amount) would collapse all three rows into a single number — OVER (ORDER BY month) instead tells it "sum everything from the start up through the current row," recalculated fresh at every row. Adding PARTITION BY resets that running calculation separately within each group:

SELECT category, order_date, amount,
       SUM(amount) OVER (PARTITION BY category ORDER BY order_date) AS category_running_total
FROM orders;

-- Electronics gets its own running total, Clothing gets its own separate one --
-- PARTITION BY splits the window into independent groups, the same way
-- GROUP BY splits rows into groups, just without collapsing them into one row each

5. LAG & LEAD: Comparing a Row to Its Neighbor

LAG and LEAD pull a value from a different row — the previous one or the next one, in a given order — directly into the current row, which is exactly what a month-over-month comparison needs:

SELECT month, total_amount,
       LAG(total_amount)  OVER (ORDER BY month) AS prev_month_amount,
       total_amount - LAG(total_amount) OVER (ORDER BY month) AS change_from_prev,
       LEAD(total_amount) OVER (ORDER BY month) AS next_month_amount
FROM monthly_sales;

-- month     total_amount   prev_month_amount   change_from_prev   next_month_amount
-- 2026-01   2390           NULL                NULL               3150
-- 2026-02   3150           2390                760                1720
-- 2026-03   1720           3150                -1430              NULL

LAG looks backward (the previous row's value), LEAD looks forward (the next row's value) — both return NULL at the boundary where no such row exists (there's no month before January, no month after March in this data). Subtracting LAG(...) from the current row's value in the same query is the standard pattern for "change since last period," computed directly in SQL instead of needing a separate calculation step afterward.

6. Hands-on Exercise

7. Knowledge Check

Four quick questions. Expand each to check your answer.