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.

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;

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.