Advanced SQL: Window Functions, CTEs & Query Patterns
SQL Fundamentals covered the core of the language: filtering, grouping, joins, subqueries. With those alone you can answer almost any question — but some answers are painful. "Each customer's previous order date", "the top three earners per department", "running revenue by day", "the whole reporting chain under Hari" — these need either convoluted self-joins or application code that pulls rows out of the database and loops over them.
This lesson covers the features that make those questions short, readable and fast: common table expressions, recursive queries, window functions, and a handful of named query patterns that recur in interviews and production alike. The second half switches perspective from "what does this query return?" to "how will the database execute it?" — writing SQL that indexes can serve, reading EXPLAIN ANALYZE, and the anti-patterns that make otherwise-correct queries slow.
Same schema as before
Every example uses the shop schema from SQL Fundamentals — The Running Example. Re-run that block to start from pristine data. A few sections add one small extra table; their CREATE TABLE and INSERT statements are included inline. For syntax lookup, keep the advanced SQL cheat sheet open.
Quick reminder of the data the results below depend on:
| order_id | customer | order_date | status | items total |
|---|---|---|---|---|
| 101 | Asha (1) | 2026-03-01 | delivered | 1350.00 |
| 102 | Asha (1) | 2026-03-15 | delivered | 240.00 |
| 103 | Ben (2) | 2026-03-20 | shipped | 420.00 |
| 104 | Chen (3) | 2026-04-02 | delivered | 300.00 |
| 105 | Ben (2) | 2026-04-10 | cancelled | 1200.00 |
| 106 | Asha (1) | 2026-05-05 | pending | 450.00 |
| 107 | (guest) | 2026-05-06 | delivered | 480.00 |
| employee_id | name | manager_id | department | salary |
|---|---|---|---|---|
| 1 | Grace | NULL | Executive | 250000 |
| 2 | Hari | 1 | Engineering | 180000 |
| 3 | Ines | 1 | Sales | 150000 |
| 4 | Jamal | 2 | Engineering | 130000 |
| 5 | Kim | 2 | Engineering | 130000 |
| 6 | Leo | 3 | Sales | 90000 |
| 7 | Maya | 4 | Engineering | 135000 |
Common Table Expressions (CTEs)
A CTE is a named subquery declared with WITH at the top of a statement. It exists only for that statement.
WITH order_totals AS (
SELECT o.order_id, o.customer_id, o.order_date,
SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY o.order_id
),
customer_spend AS (
SELECT customer_id, SUM(total) AS spend
FROM order_totals
WHERE customer_id IS NOT NULL
GROUP BY customer_id
)
SELECT c.name, cs.spend
FROM customer_spend cs
JOIN customers c USING (customer_id)
WHERE cs.spend > (SELECT AVG(spend) FROM customer_spend)
ORDER BY cs.spend DESC;
| name | spend |
|---|---|
| Asha Rao | 2040.00 |
Customer spends are Asha 2040, Ben 420, Chen 300; the average is 920; only Asha is above it.
Why CTEs Matter
The same query written with nested subqueries would reference customer_spend twice — so you would write it twice — and read inside-out. CTEs fix both problems:
| Property | Nested subqueries | CTEs |
|---|---|---|
| Reading order | Inside-out | Top-to-bottom, like a pipeline |
| Reuse within the statement | Copy-paste | Reference by name, any number of times |
| Debugging | Extract the inner query by hand | SELECT * FROM step_n to inspect any step |
| Recursion | Impossible | WITH RECURSIVE |
Think of a CTE chain as a sequence of named intermediate tables: order_totals → customer_spend → final answer. Breaking a hard question into named steps is the single best technique for writing complex SQL correctly, in interviews and at work.
CTEs and Performance: Inlined or Materialised?
A CTE is a logical construct; the engine decides whether to compute it once and store the result (materialise) or to merge it into the outer query (inline) like a view.
| Engine | Behaviour |
|---|---|
| PostgreSQL 12+ | Inlines a non-recursive, side-effect-free CTE referenced once; materialises if referenced more than once. Override with AS MATERIALIZED / AS NOT MATERIALIZED |
| PostgreSQL 11 and older | Always materialised — an "optimisation fence" that blocked predicate pushdown |
| MySQL 8 | Merges or materialises like a derived table, at the optimiser's discretion |
| SQLite | Usually inlines; AS MATERIALIZED supported since 3.35 |
The practical consequence: on modern engines, CTEs cost nothing extra in the common case. If you see an old blog post saying "CTEs are slow in Postgres", it predates version 12.
Data-Modifying CTEs
In PostgreSQL a CTE can contain INSERT, UPDATE or DELETE ... RETURNING, letting several writes run as one atomic statement:
WITH archived AS (
DELETE FROM orders
WHERE status = 'cancelled'
RETURNING *
)
SELECT count(*) AS archived_orders FROM archived; -- 1 (order 105)
All sub-statements see the same snapshot of the data, so one CTE cannot see rows another CTE in the same statement just modified — chain them through RETURNING instead. (Run this inside BEGIN ... ROLLBACK to keep order 105 for later sections.)
Recursive CTEs
A recursive CTE references itself. It is how SQL walks hierarchies and graphs whose depth is unknown in advance — something a fixed number of self-joins cannot do.
WITH RECURSIVE name AS (
anchor_query -- runs once: the starting rows
UNION ALL
recursive_query -- references "name"; runs repeatedly
)
SELECT ... FROM name;
The engine evaluates it iteratively, not by true recursion:
flowchart TD
A["Run the anchor query"] --> B["Result rows go to the output<br/>and to the working table"]
B --> C["Run the recursive query using<br/>ONLY the working table's rows"]
C --> D{"Did it produce any rows?"}
D -->|"yes"| E["New rows go to the output<br/>and replace the working table"]
E --> C
D -->|"no"| F["Stop and return all output rows"]
Each iteration sees only the rows produced by the previous iteration, not the whole accumulated result. Iteration stops when a step produces nothing — so the recursive part must eventually produce no rows, or the query runs forever.
Org Chart: Walking Down a Hierarchy
Everyone in the company with their depth and reporting path:
WITH RECURSIVE org AS (
-- anchor: the root (no manager)
SELECT employee_id, name, manager_id,
1 AS depth,
name::text AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- recursive step: direct reports of the previous level
SELECT e.employee_id, e.name, e.manager_id,
org.depth + 1,
org.path || ' > ' || e.name
FROM employees e
JOIN org ON e.manager_id = org.employee_id
)
SELECT depth, path FROM org ORDER BY path;
| depth | path |
|---|---|
| 1 | Grace |
| 2 | Grace > Hari |
| 3 | Grace > Hari > Jamal |
| 4 | Grace > Hari > Jamal > Maya |
| 3 | Grace > Hari > Kim |
| 2 | Grace > Ines |
| 3 | Grace > Ines > Leo |
Iteration by iteration: the anchor yields Grace; step 1 finds Hari and Ines (manager = Grace); step 2 finds Jamal, Kim (Hari) and Leo (Ines); step 3 finds Maya (Jamal); step 4 finds nobody whose manager is Maya, so recursion stops. Sorting by the path string produces a depth-first, indented-tree order.
Type of the recursive column
name::text in the anchor is not decoration. The column type is fixed by the anchor; if name were varchar(50), path would be varchar(50) and the concatenation in the recursive step would fail with a type mismatch. Cast anchor columns to the widest type the recursion will produce. (MySQL silently truncates instead — CAST(name AS CHAR(1000)) in the anchor.)
Subtree aggregation — total payroll of Hari's organisation, Hari included:
WITH RECURSIVE team AS (
SELECT employee_id, salary FROM employees WHERE name = 'Hari'
UNION ALL
SELECT e.employee_id, e.salary
FROM employees e JOIN team t ON e.manager_id = t.employee_id
)
SELECT COUNT(*) AS headcount, SUM(salary) AS payroll FROM team;
| headcount | payroll |
|---|---|
| 4 | 575000.00 |
Hari 180000 + Jamal 130000 + Kim 130000 + Maya 135000.
Walking up — Maya's management chain — just reverses the join direction (JOIN chain c ON e.employee_id = c.manager_id), yielding Maya, Jamal, Hari, Grace.
Graph Traversal With Cycle Protection
Hierarchies are trees; general graphs have cycles. Add a small flight network:
CREATE TABLE routes (src text, dst text, PRIMARY KEY (src, dst));
INSERT INTO routes VALUES
('BOM', 'DXB'), ('BOM', 'SIN'), ('SIN', 'SYD'), ('SIN', 'DXB'),
('DXB', 'LHR'), ('LHR', 'JFK'), ('JFK', 'LHR'); -- LHR <-> JFK is a cycle
flowchart LR
BOM --> DXB
BOM --> SIN
SIN --> SYD
SIN --> DXB
DXB --> LHR
LHR --> JFK
JFK --> LHR
A naive recursive walk from BOM would loop LHR → JFK → LHR forever. Carry the visited path as an array and refuse to revisit:
WITH RECURSIVE reach AS (
SELECT dst, 1 AS hops, ARRAY[src, dst] AS path
FROM routes WHERE src = 'BOM'
UNION ALL
SELECT r.dst, reach.hops + 1, reach.path || r.dst
FROM reach
JOIN routes r ON r.src = reach.dst
WHERE NOT r.dst = ANY (reach.path) -- cycle guard
)
SELECT DISTINCT ON (dst) dst, hops, array_to_string(path, ' > ') AS route
FROM reach
ORDER BY dst, hops; -- shortest route per destination
| dst | hops | route |
|---|---|---|
| DXB | 1 | BOM > DXB |
| JFK | 3 | BOM > DXB > LHR > JFK |
| LHR | 2 | BOM > DXB > LHR |
| SIN | 1 | BOM > SIN |
| SYD | 2 | BOM > SIN > SYD |
The reach CTE itself produces 8 rows — every simple path from BOM (for example, DXB is reachable in 1 hop directly and in 2 via SIN). DISTINCT ON (PostgreSQL-only) keeps the first row per destination in the ORDER BY order, i.e. the fewest hops.
PostgreSQL 14+ has built-in syntax for both concerns: SEARCH BREADTH FIRST BY dst SET ord and CYCLE dst SET is_cycle USING path. MySQL and SQLite lack arrays; carry the path as a delimited string and test with LOCATE/instr. As a safety net in any engine, add a depth limit (WHERE reach.hops < 10).
SQL is not a graph database
Recursive CTEs are excellent for trees and modest graphs (org charts, category trees, bill-of-materials, dependency graphs with thousands of nodes). Path enumeration on dense graphs grows exponentially; for "friends of friends of friends" across millions of users, see Graph Databases.
Generating Series
Reports need rows for days that have no data. Daily order counts for the first week of May, including zero days:
SELECT d::date AS day, COUNT(o.order_id) AS orders
FROM generate_series(DATE '2026-05-01', DATE '2026-05-07', INTERVAL '1 day') AS d
LEFT JOIN orders o ON o.order_date = d::date
GROUP BY d
ORDER BY d;
| day | orders |
|---|---|
| 2026-05-01 | 0 |
| 2026-05-02 | 0 |
| 2026-05-03 | 0 |
| 2026-05-04 | 0 |
| 2026-05-05 | 1 |
| 2026-05-06 | 1 |
| 2026-05-07 | 0 |
generate_series is PostgreSQL-specific. Everywhere else, a recursive CTE produces the same calendar:
WITH RECURSIVE days (day) AS (
SELECT DATE '2026-05-01'
UNION ALL
SELECT day + 1 FROM days WHERE day < DATE '2026-05-07' -- MySQL: day + INTERVAL 1 DAY
) -- SQLite: date(day, '+1 day')
SELECT day FROM days;
This "calendar table LEFT JOIN facts" shape is the standard fix for charts with missing bars.
Window Functions
A window function computes a value for each row from a set of related rows — its window — without collapsing them. That is the difference from GROUP BY:
flowchart LR
subgraph G["GROUP BY department"]
G1["7 employee rows"] --> G2["3 rows: one per department"]
end
subgraph W["AVG(salary) OVER (PARTITION BY department)"]
W1["7 employee rows"] --> W2["7 rows: each keeps its detail<br/>plus its department's average"]
end
SELECT name, department, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg,
salary - AVG(salary) OVER (PARTITION BY department) AS diff
FROM employees
ORDER BY department, salary DESC;
| name | department | salary | dept_avg | diff |
|---|---|---|---|---|
| Hari | Engineering | 180000 | 143750 | 36250 |
| Maya | Engineering | 135000 | 143750 | -8750 |
| Jamal | Engineering | 130000 | 143750 | -13750 |
| Kim | Engineering | 130000 | 143750 | -13750 |
| Grace | Executive | 250000 | 250000 | 0 |
| Ines | Sales | 150000 | 120000 | 30000 |
| Leo | Sales | 90000 | 120000 | -30000 |
(PostgreSQL prints the averages with many decimal places; values are rounded here.)
Anatomy of OVER()
function(args) OVER (
PARTITION BY ... -- split rows into independent groups (optional)
ORDER BY ... -- order rows within each partition (optional)
frame_clause -- which rows around the current one to include (optional)
)
| Part | Question it answers | If omitted |
|---|---|---|
PARTITION BY | Which rows belong together? | The whole result is one partition |
ORDER BY | In what order are they considered? | No order; every row's window is the whole partition |
| Frame | Which slice relative to the current row? | With ORDER BY: start of partition up to the current row and its peers. Without: the whole partition |
Window functions are evaluated after WHERE, GROUP BY and HAVING, but before DISTINCT, ORDER BY and LIMIT. Two consequences:
- You cannot use a window function in
WHERE. To filter on one (e.g. "rank = 1"), compute it in a subquery or CTE and filter outside. - You can apply a window over aggregated results:
SUM(SUM(total)) OVER (ORDER BY month)is a running total of monthly sums.
A window definition that repeats can be named: ... OVER w ... WINDOW w AS (PARTITION BY department ORDER BY salary DESC).
Ranking: ROW_NUMBER, RANK, DENSE_RANK
The three ranking functions differ only in how they handle ties. Jamal and Kim both earn 130000:
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC, employee_id) AS row_number,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees
ORDER BY salary DESC, employee_id;
| name | salary | row_number | rank | dense_rank |
|---|---|---|---|---|
| Grace | 250000 | 1 | 1 | 1 |
| Hari | 180000 | 2 | 2 | 2 |
| Ines | 150000 | 3 | 3 | 3 |
| Maya | 135000 | 4 | 4 | 4 |
| Jamal | 130000 | 5 | 5 | 5 |
| Kim | 130000 | 6 | 5 | 5 |
| Leo | 90000 | 7 | 7 | 6 |
| Function | Ties | Gaps after ties | Use when |
|---|---|---|---|
ROW_NUMBER() | Broken arbitrarily (add a tie-breaker!) | n/a | You need exactly N rows, or deduplication |
RANK() | Same rank | Yes (5, 5, 7) | "Olympic" ranking — competition position |
DENSE_RANK() | Same rank | No (5, 5, 6) | "N-th highest distinct value" |
Make ROW_NUMBER deterministic
Without employee_id in the ORDER BY, whether Jamal or Kim gets 5 is unspecified and can change between runs. Any query that picks rows with ROW_NUMBER needs a unique tie-breaker, or you get flaky results — and flaky tests.
NTILE(n) splits ordered rows into n roughly equal buckets (quartiles, deciles); PERCENT_RANK() and CUME_DIST() give relative position between 0 and 1.
LAG and LEAD: Comparing With Neighbouring Rows
LAG(expr, n, default) reads a value from n rows before the current one in the window order; LEAD reads from n rows after. Days between each of Asha's orders:
SELECT order_id, order_date,
LAG(order_date) OVER (ORDER BY order_date) AS prev_order,
order_date - LAG(order_date) OVER (ORDER BY order_date) AS days_since_prev
FROM orders
WHERE customer_id = 1
ORDER BY order_date;
| order_id | order_date | prev_order | days_since_prev |
|---|---|---|---|
| 101 | 2026-03-01 | NULL | NULL |
| 102 | 2026-03-15 | 2026-03-01 | 14 |
| 106 | 2026-05-05 | 2026-03-15 | 51 |
For all customers at once, add PARTITION BY customer_id so one customer's first order does not "see" another customer's last. Subtracting dates yields an integer number of days in PostgreSQL; MySQL needs DATEDIFF(a, b), SQLite julianday(a) - julianday(b).
LAG/LEAD replace the classic self-join on "row n − 1", which was both slower and awkward when sequence numbers had gaps. Typical uses: period-over-period change, session gaps, detecting status transitions (WHERE status <> LAG(status) OVER (...) — in a subquery).
FIRST_VALUE, LAST_VALUE and the Frame Trap
SELECT name, salary,
FIRST_VALUE(name) OVER (ORDER BY salary DESC) AS top_earner,
LAST_VALUE(name) OVER (ORDER BY salary DESC) AS last_naive,
LAST_VALUE(name) OVER (ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS last_correct
FROM employees
WHERE department = 'Sales';
| name | salary | top_earner | last_naive | last_correct |
|---|---|---|---|---|
| Ines | 150000 | Ines | Ines | Leo |
| Leo | 90000 | Ines | Leo | Leo |
last_naive looks broken but is doing exactly what it was told. With an ORDER BY and no explicit frame, the default frame ends at the current row, so the "last value" of the window is always the current row itself. This is the most common window-function bug; the fix is an explicit frame that runs to the end of the partition.
Running Totals
Add an ORDER BY to an aggregate window and it becomes cumulative. Running revenue by order date, excluding cancelled orders:
WITH order_totals AS (
SELECT o.order_id, o.order_date, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o JOIN order_items oi USING (order_id)
WHERE o.status <> 'cancelled'
GROUP BY o.order_id
)
SELECT order_date, order_id, total,
SUM(total) OVER (ORDER BY order_date, order_id) AS running_total
FROM order_totals
ORDER BY order_date, order_id;
| order_date | order_id | total | running_total |
|---|---|---|---|
| 2026-03-01 | 101 | 1350.00 | 1350.00 |
| 2026-03-15 | 102 | 240.00 | 1590.00 |
| 2026-03-20 | 103 | 420.00 | 2010.00 |
| 2026-04-02 | 104 | 300.00 | 2310.00 |
| 2026-05-05 | 106 | 450.00 | 2760.00 |
| 2026-05-06 | 107 | 480.00 | 3240.00 |
Add PARTITION BY customer_id for a per-customer running total that resets for each customer. Before window functions, this required a correlated subquery summing "all earlier rows" for each row — O(n²) work. A window running total is a single ordered pass.
Moving Averages
A moving average uses a fixed-size frame that slides with the current row:
-- same order_totals CTE as above
SELECT order_date, total,
ROUND(AVG(total) OVER (ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM order_totals
ORDER BY order_date;
| order_date | total | moving_avg_3 |
|---|---|---|
| 2026-03-01 | 1350.00 | 1350.00 |
| 2026-03-15 | 240.00 | 795.00 |
| 2026-03-20 | 420.00 | 670.00 |
| 2026-04-02 | 300.00 | 320.00 |
| 2026-05-05 | 450.00 | 390.00 |
| 2026-05-06 | 480.00 | 410.00 |
The first two rows average over fewer than three values because the frame is clipped at the partition start. If that is misleading for your chart, null them out with CASE WHEN COUNT(*) OVER (same frame) < 3 THEN NULL ELSE ... END.
Frames: ROWS vs RANGE vs GROUPS
The frame clause defines the window relative to the current row:
ROWS | RANGE | GROUPS BETWEEN start AND end
start / end: UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW | n FOLLOWING | UNBOUNDED FOLLOWING
| Mode | Unit of "n PRECEDING" | Peers (rows with equal ORDER BY value) |
|---|---|---|
ROWS | Physical rows | Treated individually |
RANGE | A distance in the ORDER BY value (e.g. INTERVAL '30 days') | Always included together |
GROUPS | Groups of peers (PostgreSQL 11+, SQLite 3.28+) | Counted as one unit |
The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE includes all of the current row's peers. This matters whenever the ORDER BY has ties. Asha's order lines — order 101 has two lines on the same date:
SELECT o.order_date, oi.product_id, oi.quantity * oi.unit_price AS amount,
SUM(oi.quantity * oi.unit_price) OVER (ORDER BY o.order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_sum,
SUM(oi.quantity * oi.unit_price) OVER (ORDER BY o.order_date) AS range_sum
FROM orders o JOIN order_items oi USING (order_id)
WHERE o.customer_id = 1
ORDER BY o.order_date, oi.product_id;
| order_date | product_id | amount | rows_sum | range_sum |
|---|---|---|---|---|
| 2026-03-01 | 1 | 1200.00 | 1200.00 | 1350.00 |
| 2026-03-01 | 2 | 150.00 | 1350.00 | 1350.00 |
| 2026-03-15 | 4 | 240.00 | 1590.00 | 1590.00 |
| 2026-05-05 | 2 | 150.00 | 1740.00 | 2040.00 |
| 2026-05-05 | 3 | 300.00 | 2040.00 | 2040.00 |
With RANGE, both 2026-03-01 rows are peers, so each sees both — a running total "as of that date". With ROWS, each row sees only physically earlier rows — and since the window's ORDER BY does not order peers, which peer comes first (and thus the intermediate rows_sum values) is not guaranteed. Rule: if the window ORDER BY is not unique, decide deliberately between RANGE semantics and adding a tie-breaker with ROWS.
RANGE with an offset gives time-based windows that are robust to missing days — trailing 30-day revenue at each order:
SELECT order_date, total,
SUM(total) OVER (ORDER BY order_date
RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW) AS trailing_30d
FROM order_totals -- the non-cancelled CTE from above
ORDER BY order_date;
| order_date | total | trailing_30d |
|---|---|---|
| 2026-03-01 | 1350.00 | 1350.00 |
| 2026-03-15 | 240.00 | 1590.00 |
| 2026-03-20 | 420.00 | 2010.00 |
| 2026-04-02 | 300.00 | 960.00 |
| 2026-05-05 | 450.00 | 450.00 |
| 2026-05-06 | 480.00 | 930.00 |
A ROWS BETWEEN 29 PRECEDING frame would be wrong here: it counts the previous 29 rows, which span very different amounts of time when some days have many orders and others none. (RANGE with an interval offset needs PostgreSQL 11+ or MySQL 8; SQLite supports numeric offsets only, so convert dates to julianday().)
Pattern: Top-N per Group
"The top 2 earners in each department" is the canonical window-function problem. Rank within each partition, then filter in an outer query:
SELECT department, name, salary
FROM (
SELECT department, name, salary,
ROW_NUMBER() OVER (PARTITION BY department
ORDER BY salary DESC, employee_id) AS rn
FROM employees
) ranked
WHERE rn <= 2
ORDER BY department, rn;
| department | name | salary |
|---|---|---|
| Engineering | Hari | 180000 |
| Engineering | Maya | 135000 |
| Executive | Grace | 250000 |
| Sales | Ines | 150000 |
| Sales | Leo | 90000 |
What about ties? Asking for the top 3 in Engineering exposes the choice:
| Function | Engineering top 3 returns | Semantics |
|---|---|---|
ROW_NUMBER() <= 3 | Hari, Maya, Jamal | Exactly 3 rows; Kim excluded by the tie-breaker |
RANK() <= 3 | Hari, Maya, Jamal, Kim | Everyone whose position is 3 or better |
DENSE_RANK() <= 3 | Hari, Maya, Jamal, Kim | The top 3 distinct salaries |
Which one is right is a product question — ask it out loud in an interview.
Alternatives:
- PostgreSQL
DISTINCT ONfor N = 1:SELECT DISTINCT ON (department) department, name, salary FROM employees ORDER BY department, salary DESC, employee_id; LATERALjoin (below) when you have a separate "groups" table and an index on (group, sort key) — it can read exactly N index entries per group instead of ranking every row.QUALIFY(Snowflake, BigQuery, DuckDB, Databricks) filters on a window function without a subquery:... QUALIFY ROW_NUMBER() OVER (...) <= 2. PostgreSQL and MySQL do not have it.
The same pattern deduplicates data — keep the latest row per key, delete the rest:
DELETE FROM staging_customers
WHERE ctid IN ( -- ctid: PostgreSQL's physical row id
SELECT ctid FROM (
SELECT ctid, ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) AS rn
FROM staging_customers
) t WHERE rn > 1
);
Pattern: Gaps and Islands
"Find consecutive runs" — login streaks, uninterrupted uptime, consecutive seat numbers — is called the gaps and islands problem. Add a login log:
CREATE TABLE logins (
customer_id integer REFERENCES customers,
login_date date,
PRIMARY KEY (customer_id, login_date)
);
INSERT INTO logins VALUES
(1, '2026-05-01'), (1, '2026-05-02'), (1, '2026-05-03'),
(1, '2026-05-05'), (1, '2026-05-06'),
(1, '2026-05-09'),
(2, '2026-05-02'), (2, '2026-05-03');
The trick: within a consecutive run of dates, both the date and its row number increase by one per row, so date - row_number is constant across the run, and changes whenever there is a gap. That constant is an island identifier.
SELECT customer_id, login_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY login_date) AS rn,
login_date - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY login_date)::int AS island
FROM logins
WHERE customer_id = 1;
| customer_id | login_date | rn | island |
|---|---|---|---|
| 1 | 2026-05-01 | 1 | 2026-04-30 |
| 1 | 2026-05-02 | 2 | 2026-04-30 |
| 1 | 2026-05-03 | 3 | 2026-04-30 |
| 1 | 2026-05-05 | 4 | 2026-05-01 |
| 1 | 2026-05-06 | 5 | 2026-05-01 |
| 1 | 2026-05-09 | 6 | 2026-05-03 |
The island value has no meaning of its own — it is just a label that is equal within a run. Group by it:
WITH marked AS (
SELECT customer_id, login_date,
login_date - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY login_date)::int AS island
FROM logins
)
SELECT customer_id,
MIN(login_date) AS streak_start,
MAX(login_date) AS streak_end,
COUNT(*) AS days
FROM marked
GROUP BY customer_id, island
ORDER BY customer_id, streak_start;
| customer_id | streak_start | streak_end | days |
|---|---|---|---|
| 1 | 2026-05-01 | 2026-05-03 | 3 |
| 1 | 2026-05-05 | 2026-05-06 | 2 |
| 1 | 2026-05-09 | 2026-05-09 | 1 |
| 2 | 2026-05-02 | 2026-05-03 | 2 |
Gaps are the complement — use LEAD to look at the next date and keep the jumps larger than one day:
SELECT customer_id,
login_date + 1 AS gap_start,
next_login - 1 AS gap_end
FROM (
SELECT customer_id, login_date,
LEAD(login_date) OVER (PARTITION BY customer_id ORDER BY login_date) AS next_login
FROM logins
) t
WHERE next_login - login_date > 1;
| customer_id | gap_start | gap_end |
|---|---|---|
| 1 | 2026-05-04 | 2026-05-04 |
| 1 | 2026-05-07 | 2026-05-08 |
The general version of the island trick, which works for any "a new group starts when X changes" rule (not just consecutive integers), is: flag the first row of each group with CASE WHEN <condition vs LAG> THEN 1 ELSE 0 END, then a running SUM of the flags is the group number.
Pattern: Pivoting
Pivoting turns row values into columns — a report with one row per customer and one column per month. SQL requires the output columns to be known when the query is written, so pivots are built with conditional aggregation.
FILTER (Standard) and CASE (Portable)
Order counts by status per month:
SELECT date_trunc('month', order_date)::date AS month,
COUNT(*) FILTER (WHERE status = 'delivered') AS delivered,
COUNT(*) FILTER (WHERE status = 'shipped') AS shipped,
COUNT(*) FILTER (WHERE status = 'pending') AS pending,
COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders
GROUP BY 1
ORDER BY 1;
| month | delivered | shipped | pending | cancelled |
|---|---|---|---|---|
| 2026-03-01 | 2 | 1 | 0 | 0 |
| 2026-04-01 | 1 | 0 | 0 | 1 |
| 2026-05-01 | 1 | 0 | 1 | 0 |
FILTER is standard SQL supported by PostgreSQL and SQLite (3.30+). The portable form, which MySQL needs, puts a CASE inside the aggregate:
SELECT c.name,
SUM(CASE WHEN t.order_date < '2026-04-01' THEN t.total ELSE 0 END) AS mar,
SUM(CASE WHEN t.order_date >= '2026-04-01' AND t.order_date < '2026-05-01' THEN t.total ELSE 0 END) AS apr,
SUM(CASE WHEN t.order_date >= '2026-05-01' THEN t.total ELSE 0 END) AS may,
SUM(t.total) AS total
FROM order_totals t -- the non-cancelled CTE, now with customer_id
JOIN customers c USING (customer_id)
GROUP BY c.customer_id, c.name
ORDER BY total DESC;
| name | mar | apr | may | total |
|---|---|---|---|---|
| Asha Rao | 1590.00 | 0.00 | 450.00 | 2040.00 |
| Ben Carter | 420.00 | 0.00 | 0.00 | 420.00 |
| Chen Wei | 0.00 | 300.00 | 0.00 | 300.00 |
(For this query, define the order_totals CTE with o.customer_id in its SELECT list, as in the first CTE example.) Note ELSE 0 versus omitting ELSE: without it, SUM over no matching rows returns NULL, which is often what you want to distinguish "zero" from "no data".
crosstab and Dynamic Pivots
PostgreSQL's tablefunc extension provides crosstab(), and SQL Server/Oracle have a PIVOT operator. Both still require you to declare the output columns. When the set of columns is genuinely dynamic (one column per product, and products change weekly), either generate the SQL in application code, or — usually better — return the long format (customer, month, total) and let the reporting layer pivot it.
Unpivoting (columns back to rows) is a LATERAL join over a VALUES list: SELECT p.name, v.metric, v.value FROM products p CROSS JOIN LATERAL (VALUES ('price', p.price), ('stock', p.stock::numeric)) AS v(metric, value);
UPSERT: Insert or Update Atomically
"Insert this row, or update it if it already exists" is extremely common — counters, caches, sync jobs, idempotent event handlers. The naive version has a race:
Session A: SELECT ... WHERE key = 'k' → no row
Session B: SELECT ... WHERE key = 'k' → no row
Session A: INSERT 'k' → ok
Session B: INSERT 'k' → duplicate key error (or a duplicate row, without a unique constraint)
UPSERT pushes the decision into a single statement that the database resolves atomically against a unique constraint:
CREATE TABLE product_daily_stats (
product_id integer REFERENCES products,
day date,
views integer NOT NULL,
PRIMARY KEY (product_id, day)
);
INSERT INTO product_daily_stats VALUES (2, '2026-05-10', 5);
INSERT INTO product_daily_stats AS s (product_id, day, views)
VALUES (2, '2026-05-10', 3)
ON CONFLICT (product_id, day)
DO UPDATE SET views = s.views + EXCLUDED.views
RETURNING product_id, day, views;
| product_id | day | views |
|---|---|---|
| 2 | 2026-05-10 | 8 |
EXCLUDED is the row that was proposed for insertion. The conflict target (product_id, day) must match a unique index or constraint. Variants:
| Need | PostgreSQL / SQLite 3.24+ | MySQL 8 |
|---|---|---|
| Update on conflict | ON CONFLICT (cols) DO UPDATE SET c = EXCLUDED.c | ON DUPLICATE KEY UPDATE c = VALUES(c) (8.0.20+: INSERT ... AS new ... c = new.c) |
| Ignore on conflict | ON CONFLICT DO NOTHING | INSERT IGNORE (also ignores other errors — careful) |
| Conditional update | DO UPDATE SET ... WHERE s.updated_at < EXCLUDED.updated_at | c = IF(cond, new.c, c) |
Standard MERGE | PostgreSQL 15+ | Not supported |
The conditional form is how you make a sync job idempotent and order-safe: only overwrite if the incoming version is newer. Two caveats: MySQL's ON DUPLICATE KEY fires on any unique key conflict, not a named one, which surprises people with several unique indexes; and PostgreSQL's MERGE is not race-free against concurrent inserts the way ON CONFLICT is — prefer ON CONFLICT for concurrent upserts.
JSON in SQL
Relational databases now store and query semi-structured JSON. Add an attributes column to products:
ALTER TABLE products ADD COLUMN attrs jsonb;
UPDATE products SET attrs = '{"brand": "Acme", "ram_gb": 16, "tags": ["work", "portable"]}' WHERE product_id = 1;
UPDATE products SET attrs = '{"brand": "Sonic", "wireless": true, "tags": ["audio"]}' WHERE product_id = 2;
UPDATE products SET attrs = '{"brand": "Oakly", "material": "oak"}' WHERE product_id = 3;
SELECT name,
attrs ->> 'brand' AS brand, -- ->> returns text, -> returns jsonb
(attrs ->> 'ram_gb')::int AS ram_gb
FROM products
WHERE attrs ? 'brand' -- key exists
ORDER BY product_id;
| name | brand | ram_gb |
|---|---|---|
| Laptop | Acme | 16 |
| Headphones | Sonic | NULL |
| Desk | Oakly | NULL |
SELECT name FROM products WHERE attrs @> '{"wireless": true}'; -- containment: Headphones
SELECT p.name, tag
FROM products p
CROSS JOIN LATERAL jsonb_array_elements_text(p.attrs -> 'tags') AS tag;
| name | tag |
|---|---|
| Laptop | work |
| Laptop | portable |
| Headphones | audio |
A GIN index (CREATE INDEX ON products USING gin (attrs jsonb_path_ops);) makes @> containment queries fast; an expression index (CREATE INDEX ON products ((attrs ->> 'brand'));) serves equality on one key. Equivalents: MySQL has a JSON type with ->/->>, JSON_CONTAINS and JSON_TABLE, and indexes JSON via generated columns; SQLite has json_extract() and ->> (3.38+).
When to use JSON columns
JSON is great for genuinely variable attributes (product specs differ by category), payloads you store but rarely filter on, and data from external APIs. It is a poor substitute for columns you join, filter, aggregate or constrain on — you lose types, NOT NULL, foreign keys and planner statistics. If every query reads attrs ->> 'brand', brand wants to be a column. More in the advanced cheat sheet.
LATERAL Joins
A normal subquery in FROM cannot refer to other tables in the same FROM clause. LATERAL lifts that restriction: the subquery is evaluated for each row of the tables to its left, and may reference their columns — a "for each" loop in SQL. (SQL Server and Oracle call it CROSS APPLY / OUTER APPLY; MySQL supports LATERAL from 8.0.14; SQLite does not.)
The two most recent orders for every customer, keeping customers with none:
SELECT c.name, r.order_id, r.order_date
FROM customers c
LEFT JOIN LATERAL (
SELECT o.order_id, o.order_date
FROM orders o
WHERE o.customer_id = c.customer_id -- references the outer row
ORDER BY o.order_date DESC
LIMIT 2
) r ON true
ORDER BY c.customer_id, r.order_date DESC;
| name | order_id | order_date |
|---|---|---|
| Asha Rao | 106 | 2026-05-05 |
| Asha Rao | 102 | 2026-03-15 |
| Ben Carter | 105 | 2026-04-10 |
| Ben Carter | 103 | 2026-03-20 |
| Chen Wei | 104 | 2026-04-02 |
| Diana Ortiz | NULL | NULL |
| Eve Adams | NULL | NULL |
LEFT JOIN LATERAL ... ON true keeps outer rows for which the subquery returns nothing; CROSS JOIN LATERAL drops them.
Why it can beat the ROW_NUMBER version: with an index on orders (customer_id, order_date DESC), each lateral subquery is an index seek that reads exactly two entries and stops. ROW_NUMBER() OVER (PARTITION BY customer_id ...) must number every order of every customer before filtering. With 10,000 customers and 50 million orders, that is 20,000 index reads versus 50 million rows ranked. Conversely, if you need most of the rows anyway, the window version's single pass wins. Other uses: calling set-returning functions per row (jsonb_array_elements, unnest), and reusing a computed expression in several later expressions without repeating it.
Indexes From the SQL Author's Point of View
You can write a query that returns the right answer and still take a production database down. Most of the difference between a 2 ms query and a 20 s one is whether the planner can use an index — and that is decided by how you write the predicate. This section is the SQL author's checklist; the internals live in Indexing Deep Dive.
To see real plans you need real volume. Create a million-row orders table:
CREATE TABLE orders_big AS
SELECT g AS order_id,
floor(random() * 200)::int + 1 AS customer_id,
DATE '2024-01-01' + floor(random() * 700)::int AS order_date,
(ARRAY['pending','shipped','delivered','cancelled'])[floor(random() * 4)::int + 1] AS status,
round((random() * 500)::numeric, 2) AS amount
FROM generate_series(1, 1000000) AS g;
ALTER TABLE orders_big ADD PRIMARY KEY (order_id);
ANALYZE orders_big;
Sargable Predicates
A predicate is sargable (Search ARGument-able) if the database can turn it into an index range lookup. The rule: the indexed column must stand alone on one side of the comparison. Anything that transforms the column hides it from the index.
| Non-sargable (index unusable) | Sargable rewrite |
|---|---|
WHERE EXTRACT(YEAR FROM order_date) = 2025 | WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01' |
WHERE date_trunc('month', order_date) = '2025-03-01' | WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01' |
WHERE amount * 1.18 > 500 | WHERE amount > 500 / 1.18 |
WHERE lower(email) = 'asha@example.com' | Keep it, but add an expression index ON customers (lower(email)) |
WHERE name LIKE '%rao' | Leading wildcard: needs a trigram (pg_trgm) GIN index or full-text search |
WHERE customer_id::text = '42' | WHERE customer_id = 42 — compare like with like |
WHERE COALESCE(city, '') = 'London' | WHERE city = 'London' |
Implicit casts are the sneaky case: comparing a varchar column to a numeric parameter in MySQL casts the column for every row and skips the index. Make parameter types match column types.
Composite Index Column Order
A composite (multi-column) B-tree index on (a, b, c) is sorted by a, then by b within equal a, then c — like a phone book sorted by last name, then first name. It can serve:
| Query shape | Uses (customer_id, order_date)? | Why |
|---|---|---|
WHERE customer_id = 42 | Yes | Leftmost prefix |
WHERE customer_id = 42 AND order_date >= '2025-06-01' | Yes, fully | Equality on first column, range on second |
WHERE customer_id = 42 ORDER BY order_date DESC LIMIT 10 | Yes, no sort needed | Entries for customer 42 are already date-ordered |
WHERE order_date >= '2025-06-01' | Not efficiently | Dates are scattered across all customers (PostgreSQL 18 can "skip scan" low-cardinality leading columns) |
WHERE customer_id IN (1, 2) AND order_date = '2025-06-01' | Yes | Each IN value is a separate seek |
The rules of thumb:
- Equality columns first, then the range or sort column. In
(customer_id, order_date), the range onorder_dateis contiguous inside each customer. In(order_date, customer_id), a date range spans all customers andcustomer_id = 42must be checked entry by entry. - Only one range per index, effectively. After the first range condition, later columns can still filter entries but cannot narrow the scan.
- Match the
ORDER BYto the index order (or its exact reverse) to eliminate a sort — this is what makesLIMITqueries instantaneous. - "Most selective column first" is a myth when all columns are compared by equality; the query shapes you need to serve decide the order.
Covering Indexes
If every column a query needs is in the index, the database can answer from the index alone — an index-only scan — without visiting the table. In PostgreSQL, add non-key payload columns with INCLUDE:
CREATE INDEX orders_big_cust_date_idx
ON orders_big (customer_id, order_date DESC)
INCLUDE (amount);
VACUUM ANALYZE orders_big; -- updates the visibility map index-only scans rely on
Now "customer 42's ten latest orders and amounts" never touches the table heap. (We left order_id out of the SELECT in the plan below so it stays covered; including it would need order_id in INCLUDE too.) MySQL InnoDB secondary indexes always implicitly contain the primary key, so covering is achieved by adding columns to the key. The cost of every extra column: a bigger index and slower writes.
Reading EXPLAIN ANALYZE
EXPLAIN shows the plan the planner chose with estimated costs; EXPLAIN ANALYZE actually runs the query and adds real timings and row counts. (It really executes it — wrap writes in BEGIN ... ROLLBACK.) Add BUFFERS to see pages read.
Before any secondary index exists:
EXPLAIN ANALYZE
SELECT order_date, amount FROM orders_big
WHERE customer_id = 42 ORDER BY order_date DESC LIMIT 10;
Limit (cost=21039.17..21039.19 rows=10 width=10) (actual time=71.802..71.806 rows=10 loops=1)
-> Sort (cost=21039.17..21051.66 rows=4997 width=10) (actual time=71.800..71.802 rows=10 loops=1)
Sort Key: order_date DESC
Sort Method: top-N heapsort Memory: 25kB
-> Seq Scan on orders_big (cost=0.00..20931.19 rows=4997 width=10) (actual time=0.019..70.911 rows=5012 loops=1)
Filter: (customer_id = 42)
Rows Removed by Filter: 994988
Planning Time: 0.097 ms
Execution Time: 71.834 ms
After creating the covering index above:
Limit (cost=0.42..1.19 rows=10 width=10) (actual time=0.028..0.034 rows=10 loops=1)
-> Index Only Scan using orders_big_cust_date_idx on orders_big (cost=0.42..384.86 rows=4997 width=10) (actual time=0.027..0.032 rows=10 loops=1)
Index Cond: (customer_id = 42)
Heap Fetches: 0
Planning Time: 0.131 ms
Execution Time: 0.051 ms
(Numbers are illustrative — yours will differ with hardware and random data — but the shape and the roughly thousand-fold difference are what you should see.)
flowchart BT
subgraph Before["Before: 71.8 ms"]
S1["Seq Scan on orders_big<br/>reads 1,000,000 rows, keeps 5,012"] --> S2["Sort, top-N heapsort"] --> S3["Limit 10"]
end
subgraph After["After: 0.05 ms"]
I1["Index Only Scan<br/>reads 10 index entries"] --> I2["Limit 10"]
end
How to read a plan:
| What to look at | Meaning | Red flag |
|---|---|---|
| Tree shape | Read inside-out: the most indented nodes run first and feed their parents | — |
cost=startup..total | Planner's estimate in arbitrary units | Only comparable between plans for the same query |
rows= estimated vs actual ... rows= | Cardinality estimate vs reality | Off by 10x or more: stale statistics or correlated columns; run ANALYZE |
loops= | How many times the node ran | Actual time and rows are per loop — multiply |
Rows Removed by Filter | Rows read then thrown away | Large numbers relative to rows returned: missing or unusable index |
Seq Scan on a large table | Full table read | Fine for small tables or when most rows are needed; bad for selective lookups |
Sort Method: external merge Disk: | Sort spilled to disk | Raise work_mem for this query, or let an index provide the order |
Heap Fetches on an index-only scan | Table visits needed for visibility checks | High: the table needs VACUUM |
Nested Loop with a large outer side | Inner side runs once per outer row | Fine for small outer sides; disastrous when estimates were wrong |
The single most valuable habit: compare estimated rows with actual rows at every node. Almost every bad plan traces back to a wrong estimate somewhere below it. The full treatment — cost model, join algorithms, statistics — is in Query Planning & Optimization; PostgreSQL-specific tuning is in PostgreSQL Performance Tuning. MySQL's equivalent is EXPLAIN ANALYZE (8.0.18+) or EXPLAIN FORMAT=TREE; SQLite has EXPLAIN QUERY PLAN.
Common Anti-Patterns
SELECT *
SELECT * in application code fetches columns you do not use (more I/O and network, including large text/jsonb values that are stored out of line), defeats covering indexes, and breaks code silently when a column is added, renamed or reordered. It is fine at an interactive prompt and inside EXISTS (SELECT * ...). In code, name the columns.
The N+1 Query Problem
An ORM loop that loads a list and then lazily loads a relation for each item:
customers = db.query("SELECT customer_id, name FROM customers") # 1 query
for c in customers:
c.orders = db.query("SELECT * FROM orders WHERE customer_id = %s", c.customer_id) # N queries
sequenceDiagram
participant App
participant DB
App->>DB: SELECT customers
DB-->>App: 1000 rows
loop for each of 1000 customers
App->>DB: SELECT orders WHERE customer_id = ?
DB-->>App: a few rows
end
Note over App,DB: 1001 round trips. At 1 ms each that is a full second of pure latency
Each query is fast, so nothing looks slow in the database's own logs; the cost is round-trip latency multiplied by N. Fixes: one join (SELECT ... FROM customers c LEFT JOIN orders o ...), or two queries with the second batched (WHERE customer_id = ANY($1) / IN (...)), which is what ORM features like select_related/prefetch_related, includes or JOIN FETCH generate. Detect N+1 by counting queries per request in tests or APM.
Functions on Indexed Columns
Covered under sargable predicates: WHERE DATE(created_at) = '2026-05-05' or WHERE lower(email) = ? without an expression index forces a full scan. Rewrite to a range on the bare column, or index the expression you actually query.
OFFSET Pagination at Scale
ORDER BY created_at DESC LIMIT 20 OFFSET 200000 must produce 200,020 rows and discard 200,000. Latency grows linearly with page depth, and concurrent inserts shift rows between pages, causing duplicates and skips. Use keyset pagination — WHERE (created_at, id) < ($last_created_at, $last_id) ORDER BY created_at DESC, id DESC LIMIT 20 with an index on (created_at, id) — which costs the same for page 1 and page 10,000. See Pagination Patterns.
More Anti-Patterns
| Anti-pattern | Why it hurts | Better |
|---|---|---|
NOT IN (subquery) on a nullable column | Returns nothing if any NULL | NOT EXISTS |
SELECT COUNT(*) ... just to test existence | Counts every match | SELECT EXISTS (SELECT 1 ... ) stops at the first |
Catch-all filters: WHERE ($1 IS NULL OR status = $1) AND ($2 IS NULL OR ...) | One cached plan must handle every combination, usually badly | Build the WHERE dynamically with only the filters supplied (still parameterised) |
OR across different columns: WHERE email = $1 OR phone = $1 | Often a single scan instead of two index lookups | UNION of two indexed queries (PostgreSQL can sometimes BitmapOr) |
DISTINCT to hide join duplicates | Wrong aggregates, extra sort | Fix the fan-out — see row multiplication |
Correlated subquery in SELECT for every row of a big result | Can execute once per row | Join to a pre-aggregated derived table, or LATERAL |
Huge IN (...) lists with thousands of literals | Parse/plan overhead, plan-cache pollution | = ANY($1::int[]) with an array parameter, or join to a temp table |
| Updating millions of rows in one statement | Long locks, huge WAL, replication lag | Batch by key ranges in short transactions |
| Indexing every column "just in case" | Every write maintains every index | Index the query shapes you have; drop unused indexes (pg_stat_user_indexes) |
War Story (illustrative)
An admin dashboard listed "recent signups" with WHERE date(created_at) = current_date ORDER BY created_at DESC OFFSET $page * 50 LIMIT 50. It was instant in development with 2,000 users. A year later, with 40 million users, the page took 12 seconds and an internal crawler that paginated to the end was holding a CPU core per request. Two changes fixed it: rewriting the filter to created_at >= current_date AND created_at < current_date + 1 so the existing created_at index applied, and switching to keyset pagination. Page loads dropped to 4 ms at any depth. Neither change altered what the query returned — only whether the database could use the index it already had.
Stored Procedures, Functions and Triggers
Databases can run code: functions (return a value, callable inside queries), procedures (CALLed, may manage transactions — PostgreSQL 11+), and triggers (functions that fire automatically on INSERT/UPDATE/DELETE).
An audit trigger that records every order status change:
CREATE TABLE order_status_log (
order_id integer,
old_status text,
new_status text,
changed_at timestamptz NOT NULL DEFAULT now()
);
CREATE FUNCTION log_status_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
IF NEW.status IS DISTINCT FROM OLD.status THEN
INSERT INTO order_status_log (order_id, old_status, new_status)
VALUES (OLD.order_id, OLD.status, NEW.status);
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_order_status
AFTER UPDATE OF status ON orders
FOR EACH ROW EXECUTE FUNCTION log_status_change();
UPDATE orders SET status = 'delivered' WHERE order_id = 103;
SELECT order_id, old_status, new_status FROM order_status_log;
| order_id | old_status | new_status |
|---|---|---|
| 103 | shipped | delivered |
The log write happens in the same transaction as the update, so it can never be lost or recorded for an update that rolled back — something application-level auditing struggles to guarantee. (MySQL uses CREATE TRIGGER ... FOR EACH ROW BEGIN ... END with its own procedural syntax; SQLite triggers are SQL-only, no procedural language.)
A procedure that bundles a multi-step operation behind one call:
CREATE PROCEDURE cancel_order(p_order_id integer)
LANGUAGE plpgsql AS $$
BEGIN
UPDATE products p
SET stock = p.stock + oi.quantity
FROM order_items oi
WHERE oi.order_id = p_order_id AND oi.product_id = p.product_id;
UPDATE orders SET status = 'cancelled' WHERE order_id = p_order_id;
END;
$$;
CALL cancel_order(106);
Trade-offs
| In favour | Against |
|---|---|
| Logic runs next to the data: one round trip instead of many | Business logic split between two codebases and two languages |
| Enforced for every client — apps, scripts, admin consoles | Harder to test, debug, profile, code-review and version |
| Triggers give transactional guarantees (audit, denormalised counters) | Triggers are invisible "spooky action": an UPDATE does more than it says |
Can reduce privileges: grant EXECUTE on a procedure, not table writes | Database CPU is the most expensive, hardest-to-scale CPU you own |
| Set-based data processing without moving data | Vendor lock-in; procedural dialects differ between engines |
A reasonable stance for most product teams: use constraints generously, triggers sparingly for things that must be transactionally tied to the write (audit logs, updated_at, guarding invariants across rows), and procedures for batch data operations or when round-trip latency truly dominates. Keep business workflows in application code, where they are visible, testable and deployable with the rest of the system. Whatever lives in the database must be in version-controlled migrations — see Database Migrations.
Summary
| Need | Reach for |
|---|---|
| Break a complex query into steps | CTE chain |
| Hierarchy or graph of unknown depth | WITH RECURSIVE with a cycle guard |
| Per-row value computed from related rows | Window function |
| Previous / next row | LAG / LEAD |
| Running total, moving average | Aggregate window with an explicit frame |
| Top N per group | ROW_NUMBER / RANK in a subquery, or LATERAL + index |
| Consecutive runs | Gaps and islands: value - ROW_NUMBER() |
| Rows to columns | Conditional aggregation with FILTER or CASE |
| Insert-or-update | ON CONFLICT / ON DUPLICATE KEY on a unique constraint |
| Fast queries | Sargable predicates, composite index order, covering indexes, EXPLAIN ANALYZE |
Practice Problems
All problems use the running example schema plus the extra tables created in this lesson.
Problem 1: N-th Highest Salary per Department
Return the second-highest distinct salary in each department, with the employee names earning it. Departments with fewer than two distinct salaries should not appear.
Solution
SELECT department, name, salary
FROM (
SELECT department, name, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dr
FROM employees
) t
WHERE dr = 2
ORDER BY department;
| department | name | salary |
|---|---|---|
| Engineering | Maya | 135000 |
| Sales | Leo | 90000 |
DENSE_RANK is correct for "N-th highest distinct value": if two people tied for the top salary, RANK would skip 2 entirely. Executive has only Grace, so it has no rank-2 row and drops out naturally.
Problem 2: Month-over-Month Revenue Growth
Using non-cancelled orders, compute revenue per month and the percentage change from the previous month.
Solution
WITH monthly AS (
SELECT date_trunc('month', o.order_date)::date AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o JOIN order_items oi USING (order_id)
WHERE o.status <> 'cancelled'
GROUP BY 1
)
SELECT month, revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS pct_change
FROM monthly
ORDER BY month;
| month | revenue | pct_change |
|---|---|---|
| 2026-03-01 | 2010.00 | NULL |
| 2026-04-01 | 300.00 | -85.07 |
| 2026-05-01 | 930.00 | 210.00 |
NULLIF(..., 0) avoids division by zero. In a real report, generate the month series first and LEFT JOIN revenue onto it, otherwise a month with no orders would be silently skipped and LAG would compare non-adjacent months.
Problem 3: Login Streaks of Three or More Days
Using the logins table, return each customer's streaks of at least three consecutive days.
Solution
WITH marked AS (
SELECT customer_id, login_date,
login_date - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY login_date)::int AS island
FROM logins
)
SELECT customer_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS days
FROM marked
GROUP BY customer_id, island
HAVING COUNT(*) >= 3;
| customer_id | streak_start | streak_end | days |
|---|---|---|---|
| 1 | 2026-05-01 | 2026-05-03 | 3 |
If the source could contain duplicate dates per customer (no primary key), deduplicate first or use DENSE_RANK instead of ROW_NUMBER — duplicates would otherwise break the island arithmetic.
Problem 4: Pareto — Cumulative Share of Revenue
For non-cancelled orders, list products by revenue (highest first) with each product's cumulative percentage of total revenue.
Solution
WITH product_rev AS (
SELECT p.name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o USING (order_id)
JOIN products p USING (product_id)
WHERE o.status <> 'cancelled'
GROUP BY p.name
)
SELECT name, revenue,
ROUND(100.0 * SUM(revenue) OVER (ORDER BY revenue DESC, name
ROWS UNBOUNDED PRECEDING)
/ SUM(revenue) OVER (), 2) AS cumulative_pct
FROM product_rev
ORDER BY revenue DESC, name;
| name | revenue | cumulative_pct |
|---|---|---|
| Laptop | 1200.00 | 37.04 |
| Chair | 840.00 | 62.96 |
| Desk | 600.00 | 81.48 |
| Headphones | 600.00 | 100.00 |
Two windows: SUM(revenue) OVER () (empty window = whole result) gives the grand total on every row, and the ordered one gives the running sum. Desk and Headphones tie at 600; the tie-breaker name plus ROWS makes the running sum step through them one at a time. With the default RANGE frame they would both show 100.00.
Problem 5: Find the Full Reporting Chain
Write a query that returns Maya's management chain from her up to the CEO, with the number of levels above her.
Solution
WITH RECURSIVE chain AS (
SELECT employee_id, name, manager_id, 0 AS levels_up
FROM employees WHERE name = 'Maya'
UNION ALL
SELECT e.employee_id, e.name, e.manager_id, c.levels_up + 1
FROM employees e
JOIN chain c ON e.employee_id = c.manager_id
)
SELECT levels_up, name FROM chain ORDER BY levels_up;
| levels_up | name |
|---|---|
| 0 | Maya |
| 1 | Jamal |
| 2 | Hari |
| 3 | Grace |
Walking up joins on e.employee_id = c.manager_id (the parent of the current row); walking down joins on e.manager_id = c.employee_id. Recursion stops when Grace's manager_id is NULL, because NULL matches nothing. If the data could contain a cycle (an accidental "A manages B manages A"), add a depth limit or a visited-path array.
Problem 6: Remove Duplicates Keeping the Latest
A staging table received duplicate rows from a retried import:
CREATE TABLE staging_events (event_id int, payload text, received_at timestamptz);
INSERT INTO staging_events VALUES
(1, 'a', '2026-05-01 10:00'), (1, 'a', '2026-05-01 10:05'),
(2, 'b', '2026-05-01 11:00'),
(3, 'c', '2026-05-01 12:00'), (3, 'c', '2026-05-01 12:01'), (3, 'c', '2026-05-01 12:02');
Delete duplicates so that only the most recently received row per event_id remains, then prevent it recurring.
Solution
DELETE FROM staging_events s
USING (
SELECT ctid, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY received_at DESC) AS rn
FROM staging_events
) d
WHERE s.ctid = d.ctid AND d.rn > 1; -- deletes 3 rows
ALTER TABLE staging_events ADD PRIMARY KEY (event_id);
Remaining: (1, 10:05), (2, 11:00), (3, 12:02). Prevention: with the primary key in place, the importer uses INSERT ... ON CONFLICT (event_id) DO UPDATE SET payload = EXCLUDED.payload, received_at = EXCLUDED.received_at WHERE staging_events.received_at < EXCLUDED.received_at — idempotent retries, latest version wins. ctid is PostgreSQL-specific; in MySQL, delete via a self-join on a surrogate key, or copy the deduplicated rows into a new table.
Problem 7: Rewrite for the Index
The table orders_big has an index on (customer_id, order_date DESC) INCLUDE (amount). Why is each query slow, and how would you rewrite it?
-- (a)
SELECT SUM(amount) FROM orders_big
WHERE customer_id = 42 AND EXTRACT(YEAR FROM order_date) = 2025;
-- (b)
SELECT order_date, amount FROM orders_big
WHERE order_date >= '2025-06-01' ORDER BY order_date DESC LIMIT 10;
-- (c)
SELECT * FROM orders_big WHERE customer_id = 42 ORDER BY order_date DESC LIMIT 10;
Solution
- (a) The equality on
customer_idcan use the index, but the function onorder_datecannot be turned into a range, so all ~5,000 of customer 42's entries are read and filtered. Rewrite:AND order_date >= '2025-01-01' AND order_date < '2026-01-01'— now the index range covers exactly 2025, and the scan is index-only becauseamountis included. - (b) There is no condition on
customer_id, the leading column, so the index's date order is only per customer. The planner must scan broadly and sort. Fix: an index whose leading column isorder_date(CREATE INDEX ON orders_big (order_date DESC)) — query shapes decide indexes. - (c) The index finds the 10 rows instantly, but
SELECT *needsstatusandorder_id, which are not in the index, so it becomes an index scan with 10 heap fetches instead of an index-only scan. Here that is cheap (10 rows), which is the honest point:SELECT *hurts most when it prevents index-only scans over many rows or drags large columns across the network.
Problem 8: Each Customer's Most Expensive Product Bought
For every customer (including those who bought nothing), return the single most expensive product they have ever bought, by unit price, breaking ties by product name. Use LATERAL.
Solution
SELECT c.name AS customer, top.product, top.unit_price
FROM customers c
LEFT JOIN LATERAL (
SELECT p.name AS product, oi.unit_price
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.customer_id = c.customer_id
ORDER BY oi.unit_price DESC, p.name
LIMIT 1
) top ON true
ORDER BY c.customer_id;
| customer | product | unit_price |
|---|---|---|
| Asha Rao | Laptop | 1200.00 |
| Ben Carter | Laptop | 1200.00 |
| Chen Wei | Headphones | 150.00 |
| Diana Ortiz | NULL | NULL |
| Eve Adams | NULL | NULL |
Ben's laptop came from the cancelled order 105; whether that should count is, again, a question to ask. The equivalent window solution ranks every order line with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY unit_price DESC, name) and keeps rn = 1, then LEFT JOINs from customers to keep Diana and Eve.
Problem 9: Running Balance Per Customer With a Reset
For each customer, list their non-cancelled orders with a running spend total that resets every calendar month.
Solution
WITH order_totals AS (
SELECT o.customer_id, o.order_id, o.order_date, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o JOIN order_items oi USING (order_id)
WHERE o.status <> 'cancelled' AND o.customer_id IS NOT NULL
GROUP BY o.order_id
)
SELECT customer_id, order_id, order_date, total,
SUM(total) OVER (PARTITION BY customer_id, date_trunc('month', order_date)
ORDER BY order_date, order_id) AS month_to_date
FROM order_totals
ORDER BY customer_id, order_date;
| customer_id | order_id | order_date | total | month_to_date |
|---|---|---|---|---|
| 1 | 101 | 2026-03-01 | 1350.00 | 1350.00 |
| 1 | 102 | 2026-03-15 | 240.00 | 1590.00 |
| 1 | 106 | 2026-05-05 | 450.00 | 450.00 |
| 2 | 103 | 2026-03-20 | 420.00 | 420.00 |
| 3 | 104 | 2026-04-02 | 300.00 | 300.00 |
The reset is just another PARTITION BY key — partitions can be expressions. Because (order_date, order_id) is unique, the default RANGE frame behaves like ROWS here.