SQL in Depth

Where you are

From Chapter 04 you have the core verbs: SELECT the columns you want, WHERE to filter rows, GROUP BY to collapse groups, and a sense of how the planner turns that into work on disk. That's the vocabulary. This chapter is the grammar — the constructs that turn "I can write a query" into "I can express almost any question, correctly, and know roughly what it costs." We go deep on five things: joins, aggregation, window functions, CTEs, and set operations, then finish with how to make it all fast.

Joins, deeply

A join stitches rows from two tables together by matching a key. The five shapes you need to know differ only in what happens to rows that don't find a match:

INNER JOIN keeps only rows that match on both sides. Priya has no rentals, so she vanishes:

SELECT c.name, r.gpu_model, r.hours
FROM customers c
INNER JOIN rentals r ON r.customer_id = c.customer_id
ORDER BY c.name, r.rental_id;

LEFT JOIN keeps every row from the left table even when the right side has no match, filling the missing columns with NULL:

SELECT c.name, r.gpu_model, r.hours
FROM customers c
LEFT JOIN rentals r ON r.customer_id = c.customer_id
ORDER BY c.name, r.rental_id;

RIGHT JOIN is the mirror image (all right-table rows kept); most engineers just rewrite it as a LEFT JOIN with the tables flipped, because reading left-to-right is easier. FULL JOIN keeps unmatched rows from both sides — useful for reconciliation ("what's in A but not B, and vice versa"). CROSS JOIN has no ON clause at all; it pairs every left row with every right row (a Cartesian product), handy for generating grids like every customer × every month.

A self-join joins a table to itself — give it two aliases. Here we find pairs of rentals by the same customer on the same GPU:

SELECT a.customer_id, a.gpu_model,
       a.rental_id AS first_rental, b.rental_id AS later_rental
FROM rentals a
JOIN rentals b
  ON a.customer_id = b.customer_id
 AND a.gpu_model  = b.gpu_model
 AND a.rental_id  < b.rental_id;   -- < avoids duplicate & self-pairs

The fan-out trap (row explosion)

This is the join bug that quietly corrupts dashboards. When you join to a table that has many rows per key (one-to-many), each left row is duplicated once per match. If you then SUM a column from the left table, you sum it multiple times. Watch it happen.

Suppose each plan has a monthly fee, and we join customers to both their plan fee and their rentals:

CREATE TABLE plans (plan TEXT, monthly_fee NUMERIC);
INSERT INTO plans VALUES ('free', 0), ('pro', 50);

-- WRONG: monthly_fee gets duplicated once per rental
SELECT SUM(p.monthly_fee) AS total_fees
FROM customers c
JOIN plans   p ON p.plan = c.plan
JOIN rentals r ON r.customer_id = c.customer_id;

The fix is to aggregate to a single grain before joining, so each customer contributes one row. Collapse rentals first, then join:

-- RIGHT: each customer is one row before we touch fees
SELECT SUM(p.monthly_fee) AS total_fees
FROM customers c
JOIN plans p ON p.plan = c.plan
WHERE c.customer_id IN (SELECT DISTINCT customer_id FROM rentals);

Aggregation that holds up

GROUP BY collapses many rows into one per group, and aggregate functions (SUM, COUNT, AVG, MIN, MAX) summarize each group. The total spend per customer:

SELECT customer_id,
       COUNT(*)                       AS num_rentals,
       SUM(hours * price_per_hour)    AS total_spend
FROM rentals
GROUP BY customer_id
ORDER BY customer_id;

You can group by multiple columns to get a finer breakdown — spend per customer per GPU model:

SELECT customer_id, gpu_model,
       SUM(hours * price_per_hour) AS spend
FROM rentals
GROUP BY customer_id, gpu_model
ORDER BY customer_id, gpu_model;

WHERE vs HAVING trips people up. WHERE filters rows before grouping; HAVING filters groups after grouping (so it can reference aggregates). Find customers who spent over $40 on rentals after June 1:

SELECT customer_id, SUM(hours * price_per_hour) AS spend
FROM rentals
WHERE created_at >= '2026-06-01'      -- per-row filter, runs first
GROUP BY customer_id
HAVING SUM(hours * price_per_hour) > 40;   -- per-group filter, runs after

COUNT(DISTINCT …) counts unique values, not rows — how many different GPU models each customer used:

SELECT customer_id,
       COUNT(*)                  AS rentals,
       COUNT(DISTINCT gpu_model) AS distinct_gpus
FROM rentals
GROUP BY customer_id;

Window functions

This is the construct that levels you up the most. A window function computes an aggregate-like value across a set of rows — but unlike GROUP BY, it keeps every original row. That's the whole idea: GROUP BY collapses, a window annotates.

The syntax is a function followed by OVER (...). Inside the parentheses, PARTITION BY splits rows into groups (like GROUP BY, but the rows stay), and ORDER BY orders rows within each partition (which matters for running totals and ranks). Add each customer's total beside every one of their rentals:

SELECT rental_id, customer_id,
       hours * price_per_hour AS spend,
       SUM(hours * price_per_hour) OVER (PARTITION BY customer_id) AS customer_total
FROM rentals
ORDER BY customer_id, rental_id;

Running totals and the window frame

Add ORDER BY inside OVER and the window becomes cumulative — by default it frames "all rows from the start of the partition up to the current row." That gives a running total:

SELECT customer_id, created_at,
       hours * price_per_hour AS spend,
       SUM(hours * price_per_hour)
         OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total
FROM rentals
ORDER BY customer_id, created_at;

Ranking: ROW_NUMBER, RANK, DENSE_RANK

These number rows within each partition by the ORDER BY. Rank each customer's rentals from most to least expensive:

SELECT customer_id, rental_id,
       hours * price_per_hour AS spend,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY hours*price_per_hour DESC) AS rn,
       RANK()       OVER (PARTITION BY customer_id ORDER BY hours*price_per_hour DESC) AS rnk
FROM rentals
ORDER BY customer_id, rn;

Looking at neighbors: LAG and LEAD

LAG reaches to the previous row, LEAD to the next — perfect for "change since last time." Days between a customer's consecutive rentals:

SELECT customer_id, created_at,
       LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_date,
       created_at - LAG(created_at)
         OVER (PARTITION BY customer_id ORDER BY created_at) AS days_since_prev
FROM rentals
ORDER BY customer_id, created_at;

CTEs & subqueries

As queries grow, nesting subqueries inside subqueries becomes unreadable. A Common Table Expression (WITH) lets you name a query and reference it like a temporary table — reading top to bottom instead of inside out. Compare a subquery and a CTE doing the same thing:

-- subquery: read inside-out
SELECT customer_id, total
FROM (SELECT customer_id, SUM(hours*price_per_hour) AS total
      FROM rentals GROUP BY customer_id) t
WHERE total > 30;

-- same logic as a CTE: read top-to-bottom
WITH spend AS (
  SELECT customer_id, SUM(hours*price_per_hour) AS total
  FROM rentals
  GROUP BY customer_id
)
SELECT customer_id, total
FROM spend
WHERE total > 30;

You can chain multiple CTEs, each building on the last — this is the readable way to express a multi-stage transformation:

WITH per_customer AS (
  SELECT customer_id, SUM(hours*price_per_hour) AS total
  FROM rentals GROUP BY customer_id
),
ranked AS (
  SELECT *, RANK() OVER (ORDER BY total DESC) AS spend_rank
  FROM per_customer
)
SELECT * FROM ranked WHERE spend_rank <= 2;

CTEs can also be recursive — a query that references itself to walk a hierarchy or generate a series. Here's the canonical "generate numbers 1–5" to show the mechanics (a base case UNION ALL a step that stops at a condition):

WITH RECURSIVE counter(n) AS (
  SELECT 1                       -- base case
  UNION ALL
  SELECT n + 1 FROM counter      -- recursive step
  WHERE n < 5                     -- stop condition
)
SELECT n FROM counter;

Set operations

These stack the results of two queries (which must have matching columns) vertically: UNION / UNION ALL combine, INTERSECT keeps rows in both, EXCEPT keeps rows in the first but not the second.

-- GPUs rented in the first half of June, OR by pro customers
SELECT gpu_model FROM rentals WHERE created_at < '2026-06-04'
UNION
SELECT r.gpu_model FROM rentals r
JOIN customers c ON c.customer_id = r.customer_id
WHERE c.plan = 'pro';

EXCEPT is a clean way to ask "what's missing." GPU models that exist in rentals but were never rented by a free customer:

SELECT DISTINCT gpu_model FROM rentals
EXCEPT
SELECT r.gpu_model FROM rentals r
JOIN customers c ON c.customer_id = r.customer_id
WHERE c.plan = 'free';

The optimization mindset

Chapter 04 showed how the planner turns SQL into physical work (index scans, hash joins, sorts). You don't tune by guessing — you tune by giving the planner less work and better access paths. A handful of habits cover most of it:

"Sargable" (Search ARGument ABLE) means a predicate the engine can satisfy with an index range. The instant you wrap an indexed column in a function — WHERE UPPER(name) = 'ADA' — the index on name can't be used and you're back to scanning every row. Rewrite to leave the column bare and transform the constant instead.

-- see the plan: is it an Index Scan or a Seq Scan? how many rows?
EXPLAIN ANALYZE
SELECT customer_id, SUM(hours*price_per_hour)
FROM rentals
WHERE created_at >= '2026-06-02'
GROUP BY customer_id;

✓ Check yourself

• Why does an INNER JOIN drop Priya but a LEFT JOIN keep her — and how would you list only customers with no rentals?
• What goes wrong if you SUM a per-customer value after joining to a one-to-many rentals table, and how do you fix it?
• In one sentence: what's the difference between GROUP BY and a window function?
• Why is WHERE date_trunc('month', created_at) = '2026-06-01' slower than a plain range filter?

SELECT customer_id,
       created_at,
       hours * price_per_hour AS spend,
       SUM(hours * price_per_hour)
         OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total
FROM rentals
ORDER BY customer_id, created_at;

Next

SQL expresses what you want from data at rest; now we pick up the language that moves, reshapes, and orchestrates data around it. → Python for Data Engineering