The queries you will actually be asked to write

There is a short list of query shapes that analytical SQL work keeps returning to: the three largest orders per customer, a running total by day, the latest row per key, several counts in one pass. This section works through that list, plus the NULL behaviours that make a query return the wrong answer without raising an error, and how to ask the database why a query is slow. You can already write a SELECT with a join and a GROUP BY; these are the patterns that sit one step past that.

Asked for                                    Pattern
"the three largest per customer"             ROW_NUMBER in a CTE, filter rn <= 3
"the latest row per user"                    the same, with ORDER BY updated_at DESC
"cost per day, cumulative"                   SUM(x) OVER (ORDER BY day)
"this month against last"                    LAG over an ordered window
"errors and calls per model, one row each"   COUNT(*) FILTER, or SUM(CASE WHEN)
"a column per category"                      conditional aggregation
"users with no events"                       NOT EXISTS
"days with no data should still appear"      generate the dates, LEFT JOIN to them
"deduplicate this table"                     rank per key, keep rn = 1

Name the steps with WITH#

A common table expression gives a subquery a name, so a long query reads top to bottom as a series of steps. Interviewers value this because they can follow your thinking, and so will you in six months. Each CTE can refer to the ones above it, which is what lets a five-stage transformation stay flat instead of nesting five subqueries deep.

WITH paid AS (
  SELECT customer_id, amount, created_at
  FROM orders
  WHERE status = 'paid'
),
per_customer AS (
  SELECT customer_id, SUM(amount) AS revenue, COUNT(*) AS orders
  FROM paid
  GROUP BY customer_id
)
SELECT c.name, p.revenue, p.orders
FROM per_customer p
JOIN customers c ON c.id = p.customer_id
ORDER BY p.revenue DESC;

Top N per group#

The most asked SQL pattern there is. Each customer's three largest orders needs a ranking that restarts for every customer, which is exactly what PARTITION BY does. Rank in a CTE, then filter on the rank outside it, because a window function cannot be referenced in the WHERE of the same SELECT that computes it.

WITH ranked AS (
  SELECT customer_id, id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, id) AS rn
  FROM orders
)
SELECT customer_id, id, amount
FROM ranked
WHERE rn <= 3;

Postgres has a shorter form for the special case of one row per group. DISTINCT ON keeps the first row of each group as defined by the ORDER BY, which must start with the same columns.

SELECT DISTINCT ON (customer_id) customer_id, id, amount, created_at
FROM orders
ORDER BY customer_id, created_at DESC, id DESC;   -- latest order per customer

Running totals, moving averages and change over time#

SELECT day,
       revenue,
       SUM(revenue) OVER (ORDER BY day)                         AS running_total,
       revenue - LAG(revenue) OVER (ORDER BY day)               AS change_vs_prev_day,
       AVG(revenue) OVER (ORDER BY day
                          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)  AS avg_7d
FROM daily_revenue;

Worth understanding once: adding ORDER BY to an OVER also changes the default frame to everything from the start of the partition up to the current row, which is why SUM becomes cumulative rather than a total. Writing ROWS BETWEEN yourself overrides that. The default is actually RANGE rather than ROWS, and the two differ when several rows share an ordering value: RANGE treats all rows with the same day as one step, so a day with three entries jumps by all three at once. If your running total looks right except where duplicates exist, write ROWS.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW    running total (the default shape)
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW           trailing 7 rows
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING           centred, 7 rows
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING   the whole partition

Keep only the latest row per key#

WITH latest AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC, id DESC) AS rn
  FROM profile_versions
)
SELECT * FROM latest WHERE rn = 1;

-- profile_versions          result
-- user  updated_at   val    user  updated_at   val
-- 1     2026-01-01   a      1     2026-02-01   b
-- 1     2026-02-01   b      2     2026-01-05   d
-- 2     2026-01-05   c
-- 2     2026-01-05   d      <- same timestamp; id DESC decides

The tempting alternative, grouping by user_id and taking MAX(updated_at), gives you the key and the timestamp but not the rest of the row, and joining back to get the rest reintroduces the tie. When a colleague's query returns more rows than there are users, this is nearly always why.

Count several things in one pass#

SELECT model,
       COUNT(*)                                   AS calls,
       COUNT(*) FILTER (WHERE status = 'error')   AS errors,
       COUNT(*) FILTER (WHERE status = 'error')::float / COUNT(*)  AS error_rate,
       COUNT(DISTINCT user_id)                    AS users,
       AVG(latency_ms)                            AS avg_latency,
       PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95_latency
FROM llm_calls
GROUP BY model;

The same trick turns rows into columns, which is what people mean by pivoting. One CASE per category inside an aggregate produces a column per category, and it is worth knowing because it is short, portable and does not need an extension.

SELECT date_trunc('day', created_at) AS day,
       SUM(CASE WHEN model = 'haiku'  THEN cost_usd ELSE 0 END) AS haiku,
       SUM(CASE WHEN model = 'sonnet' THEN cost_usd ELSE 0 END) AS sonnet,
       SUM(CASE WHEN model = 'opus'   THEN cost_usd ELSE 0 END) AS opus
FROM llm_calls
GROUP BY 1
ORDER BY 1;

Report p95 rather than the mean for latency, and be aware that averaging p95 across days is not the p95 of those days. Percentiles do not add up, so recompute them from the underlying rows over whatever period you are reporting.

NULL traps#

NULL does not mean zero and does not mean empty. It means unknown, and SQL evaluates comparisons against it to a third value that is neither true nor false. Any row whose WHERE condition evaluates to that third value is not returned, which is how a filter can quietly drop rows you meant to keep without anything appearing to go wrong.

-- tier: 'pro', NULL, 'free', 'pro'      amount: 10, 20, NULL, 5

SELECT COUNT(*) FROM t WHERE tier = NULL;              -- 0, never any rows
SELECT COUNT(*) FROM t WHERE tier IS NULL;             -- 1
SELECT COUNT(*) FROM t WHERE tier <> 'pro';            -- 1, not 2
SELECT COUNT(*) FROM t WHERE tier IS DISTINCT FROM 'pro';   -- 2, the NULL included

SELECT SUM(amount), AVG(amount), COUNT(amount), COUNT(*) FROM t;
-- 35.0 | 11.67 | 3 | 4      AVG divided by 3, not 4

SELECT 'tier: ' || tier FROM t;    -- NULL for the row where tier is NULL,
                                   -- not the string 'tier: '

• NULL = NULL is not true. Use IS NULL, or IS DISTINCT FROM when you want a comparison that treats NULL as a value.
• COUNT(column) skips NULLs, COUNT(*) counts rows, and AVG divides by the non-null count. They disagree on the same table.
• WHERE x NOT IN (subquery) returns no rows at all if the subquery contains a single NULL, because x <> NULL is unknown for every row. NOT EXISTS does not have this problem and is the habit to form.
• Concatenating or adding anything to NULL gives NULL, so one missing field blanks a whole derived string. COALESCE(col, '') fixes it deliberately.
• An outer join manufactures NULLs. A condition on the right-hand table belongs in the ON clause; put it in WHERE and you have silently turned your LEFT JOIN back into an INNER JOIN.

-- Looks like a LEFT JOIN. Behaves like an INNER JOIN.
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';          -- Katherine's NULL status fails this, so she is dropped

-- What was meant:
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid';

Why is it slow? Ask the database#

EXPLAIN ANALYZE
SELECT * FROM llm_calls WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;

-- Limit  (actual time=612.8..612.8 rows=20 loops=1)
--   ->  Sort  (actual time=612.8..612.8 rows=20 loops=1)
--         Sort Key: created_at DESC
--         Sort Method: top-N heapsort  Memory: 41kB
--         ->  Seq Scan on llm_calls  (actual time=0.3..598.1 rows=8140 loops=1)
--               Filter: (user_id = 42)
--               Rows Removed by Filter: 4991860
-- Execution Time: 613.2 ms

CREATE INDEX llm_calls_user_created_idx ON llm_calls (user_id, created_at DESC);

-- Limit  (actual time=0.04..0.07 rows=20 loops=1)
--   ->  Index Scan using llm_calls_user_created_idx on llm_calls
--         Index Cond: (user_id = 42)
-- Execution Time: 0.09 ms

Read a plan inside out, and look at three numbers on each line. Rows Removed by Filter says how much work was thrown away, which is the signal that an index would help. A large gap between the estimated rows and the actual rows means the planner's statistics are stale, and ANALYZE the_table is the fix; a plan built on a wrong estimate picks the wrong join strategy, which is how a query that ran in 50 ms starts taking two minutes after a bulk load. And loops on an inner node multiplies its cost: a Nested Loop showing loops=50000 is running the inner side fifty thousand times.

• Seq Scan on a large table where you filtered: usually a missing index, occasionally the planner correctly deciding most rows match.
• Sort with a large Memory or, worse, Disk figure: the query is sorting more than it needs to. An index in the right order removes the node.
• Nested Loop with a high loop count: fine for tens of rows, ruinous for millions. It often follows a bad row estimate.
• Hash Join with a Batches count above 1: the hash table did not fit in work_mem and spilled to disk.
• Estimated rows off by three orders of magnitude: run ANALYZE, then read the plan again. Everything downstream of a bad estimate is a bad decision.