Advanced SQL: Window Functions, CTEs, and Subqueries Questions

Direct answer

LAG(expr) reaches back to the previous row within a partition without a self-join. For day-over-day (or week-over-week) percent change, partition by the entity (symbol, store, user), order by the date column, and compute (current - LAG(current)) / LAG(current) * 100. Two guards make this production-safe: the first row per entity has no prior row, so LAG returns NULL, and that NULL must stay NULL in the output rather than being defaulted to 0 or 100%; and a prior value of exactly 0 must be guarded explicitly, or the division throws a divide-by-zero error in most engines. The identical offset idea, run in reverse, turns a table that only stores a running/cumulative balance into daily deltas: delta = current_balance - LAG(current_balance).

Structured elaboration

• LAG(expr, offset, default) reads the value from offset rows back (default 1) within PARTITION BY ... ORDER BY ...; an explicit third argument can substitute a value for missing rows, but for a percent-change calculation you almost always want the true NULL (missing prior data), not a substituted default that would print as a fake 0% change.
• Guard the divide: CASE WHEN prev IS NULL OR prev = 0 THEN NULL ELSE (current - prev) / prev * 100 END. NULLIF(prev, 0) is an equally valid, more compact way to turn a zero denominator into NULL before dividing.
• Week-over-week is the same shape at a coarser grain: either pre-aggregate to one row per (entity, week) and use LAG(weekly_value) with an offset of 1, or use LAG(daily_value, 7) only if you are certain every calendar day already has exactly one row (see the leap-year/offset trap in the sibling year-over-year question for why a fixed row offset and a fixed calendar offset are not the same guarantee).
• Cumulative-to-delta is LAG used to undo a running total: LAG on the balance column, then subtract, rather than LAG on an already-incremental value.

Worked example

-- day-over-day pct change with NULL-first-row and zero-prev guards
SELECT
  symbol,
  trade_date,
  close_price,
  LAG(close_price) OVER (PARTITION BY symbol ORDER BY trade_date) AS prev_close,
  CASE
    WHEN LAG(close_price) OVER (PARTITION BY symbol ORDER BY trade_date) IS NULL THEN NULL
    WHEN LAG(close_price) OVER (PARTITION BY symbol ORDER BY trade_date) = 0 THEN NULL
    ELSE ROUND(
      (close_price - LAG(close_price) OVER (PARTITION BY symbol ORDER BY trade_date))
      / LAG(close_price) OVER (PARTITION BY symbol ORDER BY trade_date) * 100, 2)
  END AS pct_change
FROM stock_prices
ORDER BY trade_date;

Executed against stock_prices(symbol, trade_date, close_price) = ('AAA','2024-01-01',100), ('AAA','2024-01-02',110), ('AAA','2024-01-03',0), ('AAA','2024-01-04',50):

The cumulative-to-delta direction, executed against account_balances(account_id, as_of_date, cumulative_balance) = (1,'01-01',1000), (1,'01-02',1250), (1,'01-03',1250), (1,'01-04',900):

SELECT account_id, as_of_date, cumulative_balance,
  cumulative_balance - LAG(cumulative_balance) OVER (PARTITION BY account_id ORDER BY as_of_date) AS daily_delta
FROM account_balances ORDER BY as_of_date;

gives deltas NULL, 250, 0, -350 (the third day is flat, the fourth day the balance actually dropped, which a naive "always positive" assumption about deltas would miss).

Complexity

Computing LAG requires the engine to sort each partition by the ORDER BY column before it can walk it: O(n log n) over the table if there's no supporting index, or effectively O(n) if an index already covers (entity, date) and the engine can read rows in that order without a separate sort step. Once sorted, LAG itself is one linear pass per partition, and because it only ever looks a fixed number of rows back, it only has to buffer that many rows at a time rather than the whole partition, so it doesn't carry the memory cost of an unbounded running aggregate like SUM() OVER (... UNBOUNDED PRECEDING). The sort is the part that can spill to disk at real scale: sorting hundreds of millions of rows by (entity, date) without a supporting index can exceed the engine's working-memory budget and force an external, disk-backed sort, which is the actual cost driver here, not the LAG computation itself.

Trade-offs & pitfalls

• Defaulting the first row's percent change to 0 (instead of NULL) is a common, easy-to-miss bug: it silently tells a dashboard "no change" when the truthful answer is "no data to compare against yet."
• Week-over-week built on LAG(x, 7) over a daily table is only correct if the table has zero missing days; if any day is absent, the 7-row offset lands on the wrong calendar week without erroring, so validate row-count-per-entity or pre-aggregate to weekly grain first.
• ROUND on the percent value is cosmetic and should be applied last, after the guard logic, not baked into the guard comparisons.
• The cumulative-to-delta direction assumes the balance table is strictly one row per (entity, date); duplicate rows for the same date will make the delta double-count or zero out depending on ordering, so the same tie-breaker discipline used for running totals applies here too.

Direct answer: "Last 7 readings" is a count-based request, but RANGE BETWEEN is a value-based frame: RANGE BETWEEN INTERVAL '6 seconds' PRECEDING AND CURRENT ROW doesn't mean "6 rows back," it means "every row whose timestamp is within 6 seconds of this row's timestamp." On irregular data that value window can contain anywhere from zero rows (during a quiet stretch) to dozens (during a burst), so no matter what interval you plug in, RANGE cannot deliver a stable "last 7 readings" semantic. The robust alternative is a purely positional frame, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, which always includes exactly the 7 most recent physical rows in sort order, independent of how close together or far apart their timestamps happen to be.

Structured elaboration

• Both frame types accept syntax that looks parallel (BETWEEN 6 PRECEDING AND CURRENT ROW), so it's easy to assume the "6" means the same thing in each. It doesn't: ROWS's number is a row count; RANGE's number (or interval) is a distance in the units of whatever you ORDER BY.
• On genuinely irregular timestamps, a RANGE frame's row count swings with local reading density: a burst of readings a second apart puts many rows inside even a small interval; a gap of hours puts zero preceding rows inside that same interval. There is no fixed interval value that reliably captures "the last 7" across both regimes, because RANGE was never counting rows in the first place.
• The dangerous part is that this fails silently. There's no error, no exception, just a differently-sized sum or average computed at every row depending on local reading density. It shows up downstream as a suspiciously spiky or suspiciously flat metric, not a crash, which makes it slow to diagnose.

Worked example

Table readings(id, ts, value): a burst of 4 readings 1 second apart, then a 3-hour gap, then 4 more readings 1 second apart.

SELECT id, ts, value,
  COUNT(*) OVER (ORDER BY ts
    RANGE BETWEEN INTERVAL '6 seconds' PRECEDING AND CURRENT ROW) AS n_range_6s,
  COUNT(*) OVER (ORDER BY ts
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS n_rows_7
FROM readings
ORDER BY ts;

Verified in DuckDB. n_range_6s comes back 1, 2, 3, 4, 1, 2, 3, 4: it resets to 1 right after the 3-hour gap even though that row is the 5th reading overall, because the 3-hour gap (10,800 seconds) is far outside the 6-second value window, so RANGE genuinely finds nothing preceding it within range. n_rows_7 comes back 1, 2, 3, 4, 5, 6, 7, 7: a smooth, always-correct count of the most recent readings, capped at 7, with no sensitivity to how close together the timestamps are.

Key points

• ROWS's bound is a count; RANGE's bound is a distance, and confusing the two is the entire trap.
• No choice of interval fixes RANGE for a count-based requirement on genuinely irregular data: shrink it and you miss rows during quiet stretches, widen it and you pull in far too many during bursts.
• The query never errors either way, so this bug is discovered from a metric that looks wrong, not from a stack trace.

Complexity
ROWS with a fixed count of preceding rows is O(n) after the initial O(n log n) sort: an engine can maintain the sum incrementally, dropping the row that falls off the trailing edge as it adds the new one. RANGE frames over irregular data are also computed incrementally by most engines, but because the number of rows in frame varies row to row, the per-row work is proportional to how many rows enter or leave the frame at that step, which is data-dependent rather than a fixed constant.

Edge cases

• Fewer than 7 total readings so far: both frames just use however many rows exist; this isn't a bug in either version, it's the expected boundary behavior at the start of the series.
• Duplicate timestamps: RANGE treats exactly-equal timestamps as peers (all included or all excluded together), which can push its row count up further inside a dense burst; ROWS is unaffected since it only cares about physical position.
• If the ordering column is a raw numeric epoch rather than a proper TIMESTAMP, the RANGE trap is identical, just with the units being "whatever the number represents" instead of an explicit interval; the type of the column doesn't change the underlying value-vs-count distinction.

Trade-offs & pitfalls

This isn't "always prefer ROWS." If the actual requirement is a genuine time-bounded window ("everything from the last 10 minutes," not "the last 7 readings"), RANGE with an appropriately chosen interval is the correct tool and ROWS is wrong for that job. Match the frame type to what the requirement is actually counting: a quantity of readings, or a span of time.

A common middle-ground need is "the last 7 readings, but only if they're within some sane recency window," so a stale burst from last week doesn't get blended with today's value. Neither ROWS nor RANGE alone expresses "both a max count and a max time span"; that needs ROWS for the count plus a separate filter (WHERE or HAVING) checking the oldest included timestamp, or a two-pass approach.

This is a different failure shape from a series with a handful of whole missing calendar days, where the frame boundary and the business unit of "day" still line up cleanly and only the sample size shrinks. Here the irregularity is sub-day and unbounded in either direction, which is what makes RANGE unusable for a count-based ask no matter how the interval is tuned.

Direct answer: A bill-of-materials (BOM) table describes which parts (children) go into building another part or product (the parent), and how many of each (qty). To roll up total cost for a top-level product, walk down the tree with a recursive common table expression (CTE), multiplying quantity along each path (2 of part A, each needing 3 of part B, means 6 of part B overall), then join the exploded quantities to a cost table and sum. A visited-path array, checked on every recursive step, stops a bad parent/child link from looping forever, exactly the same mechanism used for an org-chart cycle guard, just walking down a tree instead of up one.

Approach

Anchor at the root product's direct children; recursive term joins each intermediate component to its own children, multiplying the accumulated quantity at each step, and refuses to revisit any component already on the current path.

WITH RECURSIVE exploded AS (
  -- anchor: direct children of the root product
  SELECT
    b.parent_id AS root_id,
    b.child_id AS component_id,
    b.qty::numeric AS qty_needed,
    ARRAY[b.parent_id, b.child_id] AS visited
  FROM bill_of_materials b
  WHERE b.parent_id = :root_id

  UNION ALL

  -- recursive term: expand one level down, multiply quantities, guard cycles
  SELECT
    e.root_id,
    child.child_id,
    e.qty_needed * child.qty::numeric,
    e.visited || child.child_id
  FROM exploded e
  JOIN bill_of_materials child ON child.parent_id = e.component_id
  WHERE NOT (child.child_id = ANY (e.visited))
)
SELECT
  e.component_id,
  SUM(e.qty_needed) AS total_qty_required,
  cc.unit_cost,
  SUM(e.qty_needed) * cc.unit_cost AS component_cost
FROM exploded e
JOIN component_cost cc ON cc.component_id = e.component_id
GROUP BY e.component_id, cc.unit_cost
ORDER BY e.component_id;

Key points

• Multiplication, not addition, accumulates along a path: a product needing 2 of a sub-assembly that itself needs 3 of a raw part needs 6 of that raw part, not 2 + 3.
• SUM(e.qty_needed) in the final GROUP BY combines quantity contributed through every distinct path to the same component, which matters when a component is reachable more than one way through the tree (e.g., both directly under the root and indirectly through a sub-assembly).
• The visited-path array check (NOT (child.child_id = ANY (e.visited))) is the same cycle-guard pattern as an org-chart walk, just walking downward through children instead of upward through managers.

Worked example

bill_of_materials(parent_id, child_id, qty): product 1 needs 2 of part 10, and 1 of part 20 directly; part 10 needs 3 of part 100. component_cost: part 10 = 0 (a sub-assembly, not purchased directly), part 20 = 5, part 100 = 2.

Verified in PostgreSQL:

Part 100's quantity (6) is 2 (units of part 10 per product) × 3 (units of part 100 per part 10), confirming the multiplicative rollup. Total product cost = 0 + 5 + 12 = 17.

graph TD
  Product1["Product 1"] -->|qty 2| Part10["Part 10"]
  Product1 -->|qty 1| Part20["Part 20 ($5)"]
  Part10 -->|qty 3| Part100["Part 100 ($2)"]

Complexity
Worst case, a BOM tree with branching factor b and depth d expands to O(b^d) exploded rows before deduplication, because a recursive CTE re-derives every distinct path rather than reusing work across shared sub-trees (a plain recursive join does not memoize). An index on bill_of_materials(parent_id) keeps each individual recursive step an efficient lookup rather than a full scan, but it does not change that exponential worst case; for a genuinely deep or wide BOM, that worst case is the real constraint, not query planning.

Edge cases

• Cycle in the data (a bad edit makes part A a child of one of its own descendants): the visited-array check stops recursion the moment a component would revisit a node already on its own path, the same way it did for the org-chart cycle test; without a depth cap as a second line of defense, a query that (incorrectly) omitted the visited-array check entirely would not terminate on cyclic data.
• A component reachable via two different paths through the tree with different accumulated quantities: both paths are legitimately summed at the final GROUP BY; this is correct, not double counting, as long as the two paths represent genuinely separate physical usages.
• Fractional quantities and rounding: quantities are cast to numeric here to avoid integer-division truncation partway through a multi-level multiplication; decide the rounding point deliberately (round only the final cost, not intermediate quantities) to avoid compounding rounding error across many levels.

Trade-offs & pitfalls

Deep BOMs are memory- and CPU-heavy for a recursive CTE specifically because of the exponential-worst-case expansion above; a genuinely deep or frequently-queried BOM benefits from precomputing and persisting a flattened, transitive-closure table (updated when the BOM itself changes) rather than recomputing this recursive query on every cost lookup. This same "aggregate a multiplicative or additive quantity up (or down) a tree, with a cycle guard" competency recurs under several different domain names: rolling up account balances through an organizational hierarchy per reporting date, rolling up cumulative revenue through a product-category tree, and rolling up referral-network revenue to a bounded depth (comparable to an affiliate-commission structure capped at, say, 3 levels). The tree-walking and cycle-safety mechanics are identical across all of them; only the aggregation operator (multiply vs. sum) and the business meaning of the nodes change.

Window functions are evaluated after WHERE filters rows, so a window function's result doesn't exist yet at the point WHERE runs; SQL simply doesn't allow referencing one there (or in GROUP BY, for the same reason). To filter on a cumulative spend computed with a window function, compute it first in a CTE (common table expression, a named WITH block) or subquery, then filter the outer query against that already-computed column.

Approach

WITH customer_year_spend AS (
  SELECT
    customer_id,
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (
      PARTITION BY customer_id, EXTRACT(YEAR FROM order_date)
      ORDER BY order_date, order_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_spend
  FROM sales
)
SELECT DISTINCT customer_id
FROM customer_year_spend
WHERE cumulative_spend > 1000;

Key points

• The window function runs inside the CTE, over the full row set; the outer query's WHERE filters the CTE's already-materialized output, which is a normal column at that point, not a window function call.
• The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame matters: without it, the default frame for an ORDER BY-bearing window is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which groups every row sharing the same ORDER BY value (here, the same order_date) into one combined total for all of them, not a true row-by-row running sum.
• Because amount is non-negative, the cumulative sum only ever increases through the year, so "the running total ever exceeds 1000" and "the year-end total exceeds 1000" pick out the exact same set of customers; DISTINCT customer_id over any row that clears the threshold is equivalent to filtering on the final total.

Why ROWS matters here: the RANGE default-frame trap

Two orders for the same customer on the same order_date (200 on 2024-03-10 and 150 on 2024-03-10, following an earlier 100 on 2024-01-05) expose the bug directly. With the default RANGE frame, both same-day rows are treated as peers and both get the combined total of all peers as their cumulative value (450 for both), instead of the running total accumulating row by row (300 after the first of the two, 450 after the second). ROWS fixes this because it counts physical rows up to the current one, not "all rows with the same ORDER BY value."

(Verified by executing both the ROWS-framed and RANGE-framed versions of this window against the same data in SQLite 3.51; the RANGE result matches the buggy pattern exactly as shown.)

Complexity

Computing the window function is one ordered pass per partition: O(n log n) if the engine has to sort by (customer_id, order_date, order_id), or close to O(n) if an existing index already provides that order. The outer WHERE/DISTINCT is a single O(n) filter and de-duplication pass over the CTE's output. This is the same total cost as computing a plain GROUP BY/HAVING total would be; the window function version costs more only in the sense that it materializes a row per order instead of collapsing straight to one row per customer.

Edge cases

• Same-order_date orders: handled correctly only with the explicit ROWS frame, as shown above; this is the single most common way this pattern silently breaks.
• Negative amounts (refunds): if amount can go negative, the cumulative sum is no longer monotonically increasing, and "any row exceeds the threshold" is no longer equivalent to "the year-end total exceeds the threshold"; in that case you must filter on the maximum cumulative value per customer-year, not just any exceeding row, or decide explicitly which semantics the business actually wants.
• Customers with zero orders in the year: they never appear in sales at all for that year, so they're correctly absent from the result without any extra handling.

Trade-offs and pitfalls

If the business question is genuinely just "whose total for the year exceeds the threshold," a plain GROUP BY customer_id HAVING SUM(amount) > 1000 computes the same customer set with less work: one aggregation pass, no window function, no CTE wrapper, and no risk of the RANGE/ROWS frame trap at all. The window-function-plus-CTE form earns its keep when the question is really about the running value, not just the total: for example, flagging the exact order at which a customer crossed a loyalty threshold, not just whether they crossed it by year-end. Reach for GROUP BY/HAVING when you only need the final number, and reach for the windowed CTE when the row-by-row trajectory itself is part of what's being asked.

Direct answer: Reach for EXISTS instead of a JOIN when you only need to know whether a related row exists and you do not want the left table's rows duplicated by the join. A LEFT JOIN (or INNER JOIN) produces one output row per matching right-side row, so an order with three payment rows shows up three times; EXISTS returns a single boolean per order and never multiplies anything, because it only asks "is there at least one match," not "give me every match." This is a separate problem from the NOT IN / NULL trap: it is about row multiplication from the join's cardinality, not about NULLs breaking a comparison.

Structured elaboration

• A JOIN's output cardinality is driven by how many rows on the right side match each row on the left. If payments.order_id is not unique per order, orders JOIN payments returns one row per (order, payment) pair, not one row per order.
• EXISTS is a correlated subquery that the engine evaluates as true/false per outer row; it stops at the first matching payment and never expands the result set.
• If all you need is a flag ("does this order have a payment yet") or you're filtering on existence (WHERE EXISTS (...)), EXISTS is both correct and typically cheaper, because it doesn't have to materialize every matching payment row just to throw most of them away.
• If you genuinely need columns FROM the right table (e.g. the payment amount), you cannot avoid a JOIN, but you can avoid duplication by picking exactly one right-side row per left-side row first (a ROW_NUMBER() filtered to 1, or an aggregate like MAX(amount)), then joining to that pre-reduced result instead of the raw table.

Worked example (executed in DuckDB)

-- orders(id, user_id, total); payments(id, order_id, amount)
CREATE TABLE orders (id INT, user_id INT, total INT);
INSERT INTO orders VALUES (100, 123, 50);

CREATE TABLE payments (id INT, order_id INT, amount INT);
INSERT INTO payments VALUES (1,100,20), (2,100,20), (3,100,10);  -- three partial payments on the same order

-- LEFT JOIN: one order silently becomes three rows
SELECT o.id AS order_id, o.total, p.id AS payment_id
FROM orders o
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.user_id = 123;
-- -> (100,50,1), (100,50,2), (100,50,3)   -- the order row is tripled

-- EXISTS: exactly one row, no duplication
SELECT o.id AS order_id, o.total,
       EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id) AS has_payment
FROM orders o
WHERE o.user_id = 123;
-- -> (100, 50, true)   -- one row, as expected for one order

If this order feeds a report that sums total per user, the LEFT JOIN version overstates that user's order total by 3x purely because of how many payments happened to exist, which is a subtle and easy-to-miss bug: the query runs fine, returns real-looking data, and the error only shows up as an inflated aggregate somewhere downstream.

Trade-offs & pitfalls

• The fix is not always "use EXISTS everywhere": if you need payments.amount in the output, EXISTS can't give it to you, and a JOIN is genuinely required. In that case dedupe first (pick the latest or largest payment per order) rather than joining raw.
• COUNT(*) after a JOIN is a common place this bug hides: SELECT COUNT(*) FROM orders o JOIN payments p ON ... counts payments, not orders, if you meant the latter.
• Don't reach for DISTINCT as a band-aid over row multiplication; it's slower than not creating the duplicates in the first place, and it silently hides which rows are getting deduped and why.