On this page

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_idcustomerorder_datestatusitems total
101Asha (1)2026-03-01delivered1350.00
102Asha (1)2026-03-15delivered240.00
103Ben (2)2026-03-20shipped420.00
104Chen (3)2026-04-02delivered300.00
105Ben (2)2026-04-10cancelled1200.00
106Asha (1)2026-05-05pending450.00
107(guest)2026-05-06delivered480.00
employee_idnamemanager_iddepartmentsalary
1GraceNULLExecutive250000
2Hari1Engineering180000
3Ines1Sales150000
4Jamal2Engineering130000
5Kim2Engineering130000
6Leo3Sales90000
7Maya4Engineering135000

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;
namespend
Asha Rao2040.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:

PropertyNested subqueriesCTEs
Reading orderInside-outTop-to-bottom, like a pipeline
Reuse within the statementCopy-pasteReference by name, any number of times
DebuggingExtract the inner query by handSELECT * FROM step_n to inspect any step
RecursionImpossibleWITH 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.

EngineBehaviour
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 olderAlways materialised — an "optimisation fence" that blocked predicate pushdown
MySQL 8Merges or materialises like a derived table, at the optimiser's discretion
SQLiteUsually 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;
depthpath
1Grace
2Grace > Hari
3Grace > Hari > Jamal
4Grace > Hari > Jamal > Maya
3Grace > Hari > Kim
2Grace > Ines
3Grace > 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;
headcountpayroll
4575000.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
dsthopsroute
DXB1BOM > DXB
JFK3BOM > DXB > LHR > JFK
LHR2BOM > DXB > LHR
SIN1BOM > SIN
SYD2BOM > 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;
dayorders
2026-05-010
2026-05-020
2026-05-030
2026-05-040
2026-05-051
2026-05-061
2026-05-070

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;
namedepartmentsalarydept_avgdiff
HariEngineering18000014375036250
MayaEngineering135000143750-8750
JamalEngineering130000143750-13750
KimEngineering130000143750-13750
GraceExecutive2500002500000
InesSales15000012000030000
LeoSales90000120000-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)
)
PartQuestion it answersIf omitted
PARTITION BYWhich rows belong together?The whole result is one partition
ORDER BYIn what order are they considered?No order; every row's window is the whole partition
FrameWhich 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:

  1. 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.
  2. 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;
namesalaryrow_numberrankdense_rank
Grace250000111
Hari180000222
Ines150000333
Maya135000444
Jamal130000555
Kim130000655
Leo90000776
FunctionTiesGaps after tiesUse when
ROW_NUMBER()Broken arbitrarily (add a tie-breaker!)n/aYou need exactly N rows, or deduplication
RANK()Same rankYes (5, 5, 7)"Olympic" ranking — competition position
DENSE_RANK()Same rankNo (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_idorder_dateprev_orderdays_since_prev
1012026-03-01NULLNULL
1022026-03-152026-03-0114
1062026-05-052026-03-1551

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';
namesalarytop_earnerlast_naivelast_correct
Ines150000InesInesLeo
Leo90000InesLeoLeo

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_dateorder_idtotalrunning_total
2026-03-011011350.001350.00
2026-03-15102240.001590.00
2026-03-20103420.002010.00
2026-04-02104300.002310.00
2026-05-05106450.002760.00
2026-05-06107480.003240.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_datetotalmoving_avg_3
2026-03-011350.001350.00
2026-03-15240.00795.00
2026-03-20420.00670.00
2026-04-02300.00320.00
2026-05-05450.00390.00
2026-05-06480.00410.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
ModeUnit of "n PRECEDING"Peers (rows with equal ORDER BY value)
ROWSPhysical rowsTreated individually
RANGEA distance in the ORDER BY value (e.g. INTERVAL '30 days')Always included together
GROUPSGroups 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_dateproduct_idamountrows_sumrange_sum
2026-03-0111200.001200.001350.00
2026-03-012150.001350.001350.00
2026-03-154240.001590.001590.00
2026-05-052150.001740.002040.00
2026-05-053300.002040.002040.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_datetotaltrailing_30d
2026-03-011350.001350.00
2026-03-15240.001590.00
2026-03-20420.002010.00
2026-04-02300.00960.00
2026-05-05450.00450.00
2026-05-06480.00930.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;
departmentnamesalary
EngineeringHari180000
EngineeringMaya135000
ExecutiveGrace250000
SalesInes150000
SalesLeo90000

What about ties? Asking for the top 3 in Engineering exposes the choice:

FunctionEngineering top 3 returnsSemantics
ROW_NUMBER() <= 3Hari, Maya, JamalExactly 3 rows; Kim excluded by the tie-breaker
RANK() <= 3Hari, Maya, Jamal, KimEveryone whose position is 3 or better
DENSE_RANK() <= 3Hari, Maya, Jamal, KimThe top 3 distinct salaries

Which one is right is a product question — ask it out loud in an interview.

Alternatives:

  • PostgreSQL DISTINCT ON for N = 1: SELECT DISTINCT ON (department) department, name, salary FROM employees ORDER BY department, salary DESC, employee_id;
  • LATERAL join (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_idlogin_daternisland
12026-05-0112026-04-30
12026-05-0222026-04-30
12026-05-0332026-04-30
12026-05-0542026-05-01
12026-05-0652026-05-01
12026-05-0962026-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_idstreak_startstreak_enddays
12026-05-012026-05-033
12026-05-052026-05-062
12026-05-092026-05-091
22026-05-022026-05-032

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_idgap_startgap_end
12026-05-042026-05-04
12026-05-072026-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;
monthdeliveredshippedpendingcancelled
2026-03-012100
2026-04-011001
2026-05-011010

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;
namemaraprmaytotal
Asha Rao1590.000.00450.002040.00
Ben Carter420.000.000.00420.00
Chen Wei0.00300.000.00300.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_iddayviews
22026-05-108

EXCLUDED is the row that was proposed for insertion. The conflict target (product_id, day) must match a unique index or constraint. Variants:

NeedPostgreSQL / SQLite 3.24+MySQL 8
Update on conflictON CONFLICT (cols) DO UPDATE SET c = EXCLUDED.cON DUPLICATE KEY UPDATE c = VALUES(c) (8.0.20+: INSERT ... AS new ... c = new.c)
Ignore on conflictON CONFLICT DO NOTHINGINSERT IGNORE (also ignores other errors — careful)
Conditional updateDO UPDATE SET ... WHERE s.updated_at < EXCLUDED.updated_atc = IF(cond, new.c, c)
Standard MERGEPostgreSQL 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;
namebrandram_gb
LaptopAcme16
HeadphonesSonicNULL
DeskOaklyNULL
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;
nametag
Laptopwork
Laptopportable
Headphonesaudio

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;
nameorder_idorder_date
Asha Rao1062026-05-05
Asha Rao1022026-03-15
Ben Carter1052026-04-10
Ben Carter1032026-03-20
Chen Wei1042026-04-02
Diana OrtizNULLNULL
Eve AdamsNULLNULL

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) = 2025WHERE 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 > 500WHERE 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 shapeUses (customer_id, order_date)?Why
WHERE customer_id = 42YesLeftmost prefix
WHERE customer_id = 42 AND order_date >= '2025-06-01'Yes, fullyEquality on first column, range on second
WHERE customer_id = 42 ORDER BY order_date DESC LIMIT 10Yes, no sort neededEntries for customer 42 are already date-ordered
WHERE order_date >= '2025-06-01'Not efficientlyDates 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'YesEach IN value is a separate seek

The rules of thumb:

  1. Equality columns first, then the range or sort column. In (customer_id, order_date), the range on order_date is contiguous inside each customer. In (order_date, customer_id), a date range spans all customers and customer_id = 42 must be checked entry by entry.
  2. Only one range per index, effectively. After the first range condition, later columns can still filter entries but cannot narrow the scan.
  3. Match the ORDER BY to the index order (or its exact reverse) to eliminate a sort — this is what makes LIMIT queries instantaneous.
  4. "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 atMeaningRed flag
Tree shapeRead inside-out: the most indented nodes run first and feed their parents—
cost=startup..totalPlanner's estimate in arbitrary unitsOnly comparable between plans for the same query
rows= estimated vs actual ... rows=Cardinality estimate vs realityOff by 10x or more: stale statistics or correlated columns; run ANALYZE
loops=How many times the node ranActual time and rows are per loop — multiply
Rows Removed by FilterRows read then thrown awayLarge numbers relative to rows returned: missing or unusable index
Seq Scan on a large tableFull table readFine for small tables or when most rows are needed; bad for selective lookups
Sort Method: external merge Disk:Sort spilled to diskRaise work_mem for this query, or let an index provide the order
Heap Fetches on an index-only scanTable visits needed for visibility checksHigh: the table needs VACUUM
Nested Loop with a large outer sideInner side runs once per outer rowFine 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-patternWhy it hurtsBetter
NOT IN (subquery) on a nullable columnReturns nothing if any NULLNOT EXISTS
SELECT COUNT(*) ... just to test existenceCounts every matchSELECT 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 badlyBuild the WHERE dynamically with only the filters supplied (still parameterised)
OR across different columns: WHERE email = $1 OR phone = $1Often a single scan instead of two index lookupsUNION of two indexed queries (PostgreSQL can sometimes BitmapOr)
DISTINCT to hide join duplicatesWrong aggregates, extra sortFix the fan-out — see row multiplication
Correlated subquery in SELECT for every row of a big resultCan execute once per rowJoin to a pre-aggregated derived table, or LATERAL
Huge IN (...) lists with thousands of literalsParse/plan overhead, plan-cache pollution= ANY($1::int[]) with an array parameter, or join to a temp table
Updating millions of rows in one statementLong locks, huge WAL, replication lagBatch by key ranges in short transactions
Indexing every column "just in case"Every write maintains every indexIndex 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_idold_statusnew_status
103shippeddelivered

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 favourAgainst
Logic runs next to the data: one round trip instead of manyBusiness logic split between two codebases and two languages
Enforced for every client — apps, scripts, admin consolesHarder 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 writesDatabase CPU is the most expensive, hardest-to-scale CPU you own
Set-based data processing without moving dataVendor 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

NeedReach for
Break a complex query into stepsCTE chain
Hierarchy or graph of unknown depthWITH RECURSIVE with a cycle guard
Per-row value computed from related rowsWindow function
Previous / next rowLAG / LEAD
Running total, moving averageAggregate window with an explicit frame
Top N per groupROW_NUMBER / RANK in a subquery, or LATERAL + index
Consecutive runsGaps and islands: value - ROW_NUMBER()
Rows to columnsConditional aggregation with FILTER or CASE
Insert-or-updateON CONFLICT / ON DUPLICATE KEY on a unique constraint
Fast queriesSargable 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;
departmentnamesalary
EngineeringMaya135000
SalesLeo90000

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;
monthrevenuepct_change
2026-03-012010.00NULL
2026-04-01300.00-85.07
2026-05-01930.00210.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_idstreak_startstreak_enddays
12026-05-012026-05-033

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;
namerevenuecumulative_pct
Laptop1200.0037.04
Chair840.0062.96
Desk600.0081.48
Headphones600.00100.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_upname
0Maya
1Jamal
2Hari
3Grace

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_id can use the index, but the function on order_date cannot 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 because amount is 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 is order_date (CREATE INDEX ON orders_big (order_date DESC)) — query shapes decide indexes.
  • (c) The index finds the 10 rows instantly, but SELECT * needs status and order_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;
customerproductunit_price
Asha RaoLaptop1200.00
Ben CarterLaptop1200.00
Chen WeiHeadphones150.00
Diana OrtizNULLNULL
Eve AdamsNULLNULL

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_idorder_idorder_datetotalmonth_to_date
11012026-03-011350.001350.00
11022026-03-15240.001590.00
11062026-05-05450.00450.00
21032026-03-20420.00420.00
31042026-04-02300.00300.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.