SQL Advanced

Window frames: ROWS vs RANGE vs GROUPS

The frame defines which rows feed the window function for the current row. Getting it wrong silently changes results — a top interview trap.

• ROWS — physical rows. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW = exactly 7 rows. Use for moving averages over a row count.
• RANGE — logical value range over the ORDER BY column. RANGE BETWEEN '7 days' PRECEDING AND CURRENT ROW = all rows whose ORDER BY value is within 7 days — row count varies. Crucially, peers (equal ORDER BY values) are lumped together.
• GROUPS — peer groups of equal ORDER BY values (Postgres 11+). Useful when ties should count as one step.

-- Time-based 7-day rolling sum that's correct even with gaps in days:
SELECT day, revenue,
  SUM(revenue) OVER (ORDER BY day
       RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) AS rev_7d
FROM daily_revenue;

-- Centered moving average (3 before, 3 after) — physical rows:
SELECT day, gpu_price,
  AVG(gpu_price) OVER (ORDER BY day
       ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS smoothed
FROM price_history;

FIRST_VALUE, LAST_VALUE, NTH_VALUE

Grab a specific row's value within the partition without a self-join. The classic gotcha: LAST_VALUE with the default frame returns the current row, not the partition's last — because the frame ends at CURRENT ROW.

SELECT customer_id, event_ts, amount,
  FIRST_VALUE(amount) OVER w AS first_amt,
  -- WRONG without full frame: returns current row
  LAST_VALUE(amount)  OVER (PARTITION BY customer_id ORDER BY event_ts
       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amt
FROM charges
WINDOW w AS (PARTITION BY customer_id ORDER BY event_ts);

Tip: "first and last in one pass" is often cleaner as MIN/MAX(... ) OVER when you only need extremes, or ROW_NUMBER-then-filter when you need whole rows.

Named windows (WINDOW clause)

When several functions share a window spec, define it once. Cleaner, less error-prone, and a subtle seniority tell.

SELECT customer_id, day, spend,
  SUM(spend)        OVER w  AS running_spend,
  AVG(spend)        OVER w  AS running_avg,
  ROW_NUMBER()      OVER w  AS day_rank
FROM daily_spend
WINDOW w AS (PARTITION BY customer_id ORDER BY day);

NTILE & percentiles

• NTILE(n) — split a partition into n roughly equal buckets (deciles, quartiles). Great for "which spend decile is this customer in?"
• PERCENTILE_CONT(p) (interpolated) vs PERCENTILE_DISC(p) (actual value). Use CONT for medians of continuous measures, DISC when you need a real observed value.
• APPROX_QUANTILES / APPROX_PERCENTILE — fast approximate percentiles on huge tables (BigQuery/Snowflake/Spark).

SELECT customer_id, gpu_hours,
  NTILE(10) OVER (ORDER BY gpu_hours) AS usage_decile,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY gpu_hours) OVER () AS median_all
FROM monthly_usage;

Set operations & data diffing

• UNION dedups (sorts — expensive); UNION ALL doesn't. Default to UNION ALL unless you truly need dedup.
• INTERSECT — rows in both. EXCEPT (a.k.a. MINUS) — rows in A not in B.
• Data diffing — to compare two tables/models row-for-row, (SELECT * FROM a EXCEPT SELECT * FROM b) UNION ALL (SELECT * FROM b EXCEPT SELECT * FROM a) returns every differing row. Invaluable when validating a refactor produces identical output.

-- Did my refactored model change any rows vs the old one? Should return 0 rows.
SELECT 'only_old' src, * FROM (SELECT * FROM model_old EXCEPT SELECT * FROM model_new) a
UNION ALL
SELECT 'only_new' src, * FROM (SELECT * FROM model_new EXCEPT SELECT * FROM model_old) b;

ROLLUP, CUBE, GROUPING SETS

Compute subtotals and grand totals in one pass instead of UNIONing many aggregates — exactly what BI summary tables need.

• ROLLUP(a, b) — hierarchy subtotals: (a,b), (a), (). Region → region+gpu → grand total.
• CUBE(a, b) — all combinations: (a,b), (a), (b), ().
• GROUPING SETS — you specify exactly which groupings you want.
• GROUPING(col) — returns 1 if the column is rolled up in that row, so you can label "All".

SELECT
  CASE WHEN GROUPING(region)=1 THEN 'ALL' ELSE region END AS region,
  CASE WHEN GROUPING(gpu_type)=1 THEN 'ALL' ELSE gpu_type END AS gpu_type,
  SUM(revenue) AS revenue
FROM fct_usage
GROUP BY ROLLUP(region, gpu_type)
ORDER BY region, gpu_type;

LATERAL joins / CROSS APPLY

A LATERAL subquery can reference columns from the table before it — letting you run a correlated, per-row subquery as a join. The canonical use: top-N per group efficiently, or calling a set-returning function per row.

-- Most recent 3 charges per customer (often faster than a window over a huge table)
SELECT c.customer_id, x.amount, x.charged_at
FROM customers c
CROSS JOIN LATERAL (
  SELECT amount, charged_at
  FROM charges ch
  WHERE ch.customer_id = c.customer_id
  ORDER BY charged_at DESC
  LIMIT 3
) x;

Window-function top-N reads the whole table; LATERAL can use an index per customer. Know both and when each wins (LATERAL shines with selective indexed lookups; windows shine when you're scanning everything anyway).

As-of (point-in-time) joins

Join a fact to the dimension/price that was in effect at the fact's timestamp — the backbone of SCD2 lookups, pricing at time-of-use, and time-series alignment. Some engines have native ASOF JOIN (Snowflake, ClickHouse, DuckDB); elsewhere you express it with a range join or a windowed lookup.

-- Price each usage row at the host's price that was active at the time.
-- Portable form: range join on a validity window.
SELECT u.usage_id, u.started_at, u.gpu_seconds, p.price_per_hr
FROM usage_events u
JOIN price_history p
  ON p.machine_id = u.machine_id
 AND u.started_at >= p.valid_from
 AND u.started_at <  p.valid_to;

-- Native (Snowflake/DuckDB):
-- SELECT ... FROM usage_events u
-- ASOF JOIN price_history p MATCH_CONDITION(u.started_at >= p.valid_from)
--   ON u.machine_id = p.machine_id;

Watch for overlapping or open-ended validity windows producing fan-out — ensure the dimension's windows are non-overlapping (a quality test worth adding).

JSON, arrays, structs

Telemetry and event data land semi-structured. Know how to extract, flatten, and aggregate it.

-- Extract a field (Postgres ->> ; Snowflake : ; BigQuery JSON_VALUE)
SELECT payload->>'gpu_model'           AS gpu_model,     -- Postgres
       (payload:gpu_count)::int        AS gpu_count      -- Snowflake
FROM raw_events;

-- Explode an array into rows
SELECT e.instance_id, tag
FROM instances e, UNNEST(e.tags) AS tag;                 -- BigQuery / Postgres: unnest()

-- Aggregate back into an array
SELECT customer_id, ARRAY_AGG(DISTINCT gpu_type) AS gpu_types
FROM usage_events GROUP BY customer_id;

Modeling note: extract hot fields into typed columns in staging; keep the raw blob for replay. Don't make BI users parse JSON.

Dates, intervals, timezones, spines

• Truncation: DATE_TRUNC('hour'|'day'|'month', ts) for bucketing.
• Timezones: store UTC, convert at the edge. ts AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles'. Be explicit — "daily revenue" depends on which timezone's midnight.
• Date spine: generate_series (Postgres) / GENERATE_DATE_ARRAY (BigQuery) to create a gap-free calendar; LEFT JOIN data onto it so missing days appear as zero.
• LOCF (last observation carried forward): fill gaps by carrying the last known value — a spine + LAST_VALUE(... IGNORE NULLS) over the ordered window.

-- IGNORE NULLS: Snowflake, BigQuery, DuckDB. PostgreSQL rejects it (see the note below).
WITH spine AS (
  SELECT generate_series(DATE '2026-01-01', CURRENT_DATE, INTERVAL '1 day')::date AS day
)
SELECT s.day,
  LAST_VALUE(p.price IGNORE NULLS) OVER (ORDER BY s.day
       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS price_locf
FROM spine s
LEFT JOIN daily_price p ON p.day = s.day;

PostgreSQL has no IGNORE NULLS. There, number the runs with COUNT(p.price) OVER (ORDER BY s.day) and take MAX(p.price) OVER (PARTITION BY that_count); each run holds one non-null price, so the maximum is that price.

Recursive CTEs

For hierarchies (host → sub-accounts, referral chains, org charts), graph traversal, and generating sequences. Structure: an anchor member UNION ALL a recursive member that references the CTE.

-- Referral depth: who referred whom, how many hops from a root
WITH RECURSIVE chain AS (
  SELECT customer_id, referrer_id, 0 AS depth
  FROM customers WHERE referrer_id IS NULL          -- anchor: roots
  UNION ALL
  SELECT c.customer_id, c.referrer_id, ch.depth + 1
  FROM customers c
  JOIN chain ch ON c.referrer_id = ch.customer_id   -- recurse
)
SELECT * FROM chain;

Always ensure termination (no cycles, or a depth cap) — a recursive CTE on cyclic data runs forever. Mention the safeguard.

Reading EXPLAIN / EXPLAIN ANALYZE

You should be able to read a plan and name the problem. Read it bottom-up (leaves run first). What to look for:

• Seq Scan on a big table where you expected an index/partition prune → missing index or a non-sargable predicate (function on the column).
• Estimated vs actual rows wildly off (EXPLAIN ANALYZE) → stale statistics; the planner is choosing a bad join order/algorithm. Fix with ANALYZE.
• Nested Loop over many rows → should likely be a hash join; often a join-key type mismatch prevents it.
• Spill to disk / external sort → not enough memory; reduce data earlier or increase work_mem.
• In warehouses: bytes scanned, partitions pruned, and whether a broadcast vs shuffle join was used.

Join algorithms (so plans make sense)

Partition pruning, clustering, skew, spill

• Partition pruning: filter on the partition column (a literal/range, not a function on it) so the engine skips partitions. The single biggest warehouse cost lever.
• Clustering / sort keys: co-locate rows by a common filter/join key so scans touch fewer micro-partitions.
• Data skew: one key with vastly more rows overloads one node in a shuffle (e.g. a whale customer, or a NULL join key). Mitigate by salting the hot key or filtering NULLs out of the join.
• Spill: aggregations/sorts exceeding memory write to disk and slow down. Reduce cardinality early, or pre-aggregate.

Approximate aggregates

On large data, exact distinct counts are expensive (they must track every value). Approximate algorithms trade a tiny error for huge speed:

• HyperLogLog — APPROX_COUNT_DISTINCT for distinct counts (~1-2% error, fraction of the cost). Great for daily active users at scale.
• Approx percentiles — APPROX_QUANTILES / t-digest for distributions.
• Sketch state — some engines let you store and merge HLL sketches across days for incremental distinct counts. Mention it for "rolling 30-day actives without rescanning."

A method for debugging a wrong query

1. Check the grain at each CTE. Add SELECT COUNT(*), COUNT(DISTINCT key) after each step — the first place they diverge is your fan-out.
2. Isolate the join. Comment out downstream logic; verify row counts pre/post each join.
3. Hunt NULLs in join keys and filter columns — the silent cause of dropped or exploded rows.
4. Compare to a known total. Reconcile a sample against a source-of-truth number you trust.
5. Diff against the prior version with EXCEPT when refactoring.

✓ Check yourself

Answer from memory before opening each one.

1. You need a trailing 7-calendar-day revenue sum, but some days have no rows. Compare ROWS BETWEEN 6 PRECEDING with RANGE BETWEEN INTERVAL '6 days' PRECEDING. Which is correct, and what does the other one do?

   Show answer

   RANGE with the interval is correct, because it measures distance in the ORDER BY value. ROWS counts seven physical rows, so across a gap it reaches back more than seven days. A date spine joined first would also fix it. See window frames.

2. LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY event_ts) returns each row's own amount instead of the customer's final one. Why, and what is the fix?

   Show answer

   The default frame ends at the current row, so the last row in the frame is the current row. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or use MAX or ROW_NUMBER if you only need an extreme or the whole row. See FIRST_VALUE and LAST_VALUE.

3. After joining usage to price_history, total cost is 8% higher than the billing system. What is the likeliest cause, and what test would have caught it?

   Show answer

   Validity windows for the same machine overlap, so some usage rows match two prices and are counted twice. Add a test that windows are disjoint per machine, or keep the latest match with ROW_NUMBER. See as-of joins.

4. A price table has rows only for Jan 1 and Jan 5. You join it to a Jan 1 to Jan 7 date spine and carry the last price forward. What should Jan 3 and Jan 6 show, and what happens if you run LAST_VALUE(price IGNORE NULLS) on PostgreSQL?

   Show answer

   Jan 3 shows the Jan 1 price and Jan 6 shows the Jan 5 price. PostgreSQL rejects IGNORE NULLS with a syntax error, so there you count non-null prices cumulatively and take MAX(price) per count; Snowflake, BigQuery and DuckDB accept it. See dates, intervals and spines.

5. EXPLAIN ANALYZE shows the planner expected 100 rows from a scan and got 4 million, and it chose a nested loop for the join. What do you suspect, and what do you do first?

   Show answer

   Stale statistics, so the planner picked an algorithm for a small input. Run ANALYZE on the table and re-check the plan; a hash join is the usual choice for a large equi-join. If the estimate stays wrong, look for a join-key type mismatch. See reading EXPLAIN.

6. You must report trailing 30-day active customers across two billion events a day. Choose between an exact range self-join and HyperLogLog sketches, and say what decides it.

   Show answer

   Use daily HLL sketches for a trend or dashboard: they cost a fraction of the scan, merge across days, and carry about 1 to 2% error. Use the exact count only where the figure is audited or billed. Say the error bound out loud. See approximate aggregates.

Takeaway

• The frame is part of the answer: ROWS counts physical rows, RANGE measures distance in the ORDER BY value and lumps ties, and the default is RANGE.
• LAST_VALUE needs an explicit full frame or it returns the current row.
• An as-of join multiplies cost if validity windows overlap, so test that they are disjoint.
• A date spine with a LEFT JOIN makes empty days appear; carrying a value forward needs IGNORE NULLS where supported and a count-then-max fallback on PostgreSQL.
• Read plans for sequential scans, estimates far from actual rows and spills; on a warehouse read bytes scanned and partitions pruned.
• Approximate distinct counts trade a known small error for sketches you can merge across days; keep exact counts for audited numbers.
• To debug a wrong query, compare COUNT(*) with COUNT(DISTINCT key) after each CTE; the first step where they diverge is the fan-out.

Techniques are only useful once you can apply them under a timer, so drill them on problems. → SQL Problems

Further reading

• Value Expressions — PostgreSQL docs, 2026. Section 4.2.8 defines window function calls and frame clauses, including RANGE, ROWS and GROUPS.
• Window Functions — PostgreSQL docs, 2026. Why LAST_VALUE depends on the frame, and which functions ignore it.
• WITH Queries (Common Table Expressions) — PostgreSQL docs, 2026. Recursive CTE structure and how to guard against cycles.
• Using EXPLAIN — PostgreSQL docs, 2026. Reading plans, row estimates and actual timings.
• ASOF JOIN — Snowflake docs, 2026. Native point-in-time join syntax and its match condition.
• FROM and JOIN clauses — DuckDB docs, 2026. The as-of join section, useful for trying the pattern locally.
• Approximate aggregate functions — Google Cloud docs, 2026. HLL sketches and approximate quantiles, with the documented error behaviour.