SQL Window Functions Cheatsheet Practice Exam

Question 1 SQL Window Functions Cheatsheet

Year-over-year change?

EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, EXCLUDE NO OTHERS — fine-grained frame control. Saves a separate query; planners often compute once. BigQuery supports RANGE with date/time intervals — useful for rolling time-based metrics. amount - LAG(amount, 12) OVER (PARTITION BY metric ORDER BY month)

Question 2 SQL Window Functions Cheatsheet

Days-to-next-purchase per user?

RANK() OVER (PARTITION BY category ORDER BY score DESC)

UPDATE t SET rn = sq.rn
FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY g ORDER BY ts) rn FROM t) sq
WHERE t.id = sq.id;

(x - AVG(x) OVER (PARTITION BY g)) / NULLIF(STDDEV(x) OVER (PARTITION BY g), 0) EXTRACT(DAY FROM LEAD(ts) OVER (PARTITION BY user ORDER BY ts) - ts)

Question 3 SQL Window Functions Cheatsheet

Islands example?

SELECT * FROM t1 a, LATERAL (SELECT * FROM t1 b WHERE b.ts < a.ts AND b.user_id = a.user_id ORDER BY b.ts DESC LIMIT 1) b;

SELECT col, SUM(x) OVER w, AVG(x) OVER w
FROM t
WINDOW w AS (PARTITION BY g ORDER BY ts);

day - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) * INTERVAL '1 day' — same value = same streak. DISTINCT ON (g) ... ORDER BY g, ts DESC picks first per g — concise PG-specific shortcut.

Question 4 SQL Window Functions Cheatsheet

PostgreSQL window function on JSONB?

LAG(x, 7) — 3rd arg is default for missing rows. Cast or use json operators inside aggregate: SUM((data->>'amount')::numeric) OVER (...) FILTER cleaner; CASE works on engines without FILTER (most do now).

SUM(CASE WHEN x > current_x THEN 1 ELSE 0 END) OVER (PARTITION BY g ORDER BY x DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

Question 5 SQL Window Functions Cheatsheet

Window function ORDER BY referencing computed column?

Common pattern in mart layer for ranking, deduping, retention metrics. Must reference column expression directly — alias from SELECT not usable inside OVER ORDER BY in some engines. SUM(x) FILTER (WHERE active) OVER (PARTITION BY g) — conditional aggregation. FIRST_VALUE(x) OVER (PARTITION BY g ORDER BY ts) and LAST_VALUE(x) OVER (PARTITION BY g ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Question 6 SQL Window Functions Cheatsheet

Aggregating window over JSON array?

COUNT(*) counts rows; COUNT(col) counts non-null values.

SUM(CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) > INTERVAL '30 min' THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY ts) AS session_id

DATE_DIFF(order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date), DAY) Combine jsonb_array_elements + window — usually one row per element.

Question 7 SQL Window Functions Cheatsheet

First-occurrence flag pattern?

RANK() OVER (PARTITION BY category ORDER BY sales DESC) When there is no previous row in the partition and no default supplied. ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts) = 1 — boolean treated as 1/0. Combine: GROUP BY ROLLUP for subtotals, then window over the result for cumulative breakdowns.

Question 8 SQL Window Functions Cheatsheet

Top 3 per group (no ties)?

Window works on the result rows from GROUP BY — useful for cumulative subtotals. ROW_NUMBER() ≤ 3 — arbitrary tiebreak per ORDER BY. LEAD(col, n_rows_forward, default_if_null) Entire partition.

Question 9 SQL Window Functions Cheatsheet

RESPECT NULLS vs IGNORE NULLS?

ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST. ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts) = 1 — boolean treated as 1/0. RESPECT NULLS (default) treats NULL as a valid value; IGNORE NULLS skips NULL when picking value. Use RANK() or DENSE_RANK() and filter rank ≤ 3 — includes tied items.

Question 10 SQL Window Functions Cheatsheet

Compute coefficient of variation per group?

Allowed in SELECT subqueries within DML — useful for backfills. STDDEV(x) OVER (PARTITION BY g) / NULLIF(AVG(x) OVER (PARTITION BY g), 0) (x - AVG(x) OVER (PARTITION BY g)) / NULLIF(STDDEV(x) OVER (PARTITION BY g), 0) x - MIN(x) OVER (PARTITION BY g) or MAX(x) OVER (PARTITION BY g) - x

Question 11 SQL Window Functions Cheatsheet

Window vs GROUP BY ROLLUP?

WITH x AS MATERIALIZED (...) forces materialization; can change performance. Rollup adds subtotals as new rows; windows keep all original rows and add columns. WHERE filters base rows before window evaluation. Wrap in subquery / CTE / use QUALIFY. Mostly aligned; Pandas defaults vary (e.g., rolling().mean() requires explicit min_periods).

Question 12 SQL Window Functions Cheatsheet

Standard window function in Spark SQL?

SUM(x) OVER (ORDER BY ts ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) With ORDER BY containing ties, RANGE includes all peer rows; ROWS uses physical row positions. Same syntax; uses Catalyst optimizer; requires partition for parallelism.

SELECT col, SUM(x) OVER w, AVG(x) OVER w
FROM t
WINDOW w AS (PARTITION BY g ORDER BY ts);

Question 13 SQL Window Functions Cheatsheet

COUNT(*) OVER () for total rows in same query?

Saves a separate query; planners often compute once. When there is no previous row in the partition and no default supplied. Treats peer groups (ties) as units — useful for "include all ties in window". (x - AVG(x) OVER w) / STDDEV(x) OVER w

Question 14 SQL Window Functions Cheatsheet

Window function in DBT models?

Common pattern in mart layer for ranking, deduping, retention metrics. LEAD(col, n_rows_forward, default_if_null) NTH_VALUE(col, 3) OVER (...) — 3rd value in the frame. Same syntax; uses Catalyst optimizer; requires partition for parallelism.

Question 15 SQL Window Functions Cheatsheet

Identify duplicates with ROW_NUMBER?

LEAD(col, n_rows_forward, default_if_null) ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn — filter rn > 1. ROW_NUMBER: unique sequential.
RANK: same value → same rank, leaves gaps.
DENSE_RANK: same value → same rank, no gaps. ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) AS delta

Question 16 SQL Window Functions Cheatsheet

What is a frame clause?

QUALIFY ROW_NUMBER() OVER (...) = 1 AND RANK() OVER (...) <= 3 ROWS: physical row offsets.
RANGE: based on the ORDER BY value (treats duplicates as the same logical position). Use a CTE; many planners deduplicate. Specifies which rows within the partition the function operates on (e.g., ROWS BETWEEN 3 PRECEDING AND CURRENT ROW).

Question 17 SQL Window Functions Cheatsheet

Keep latest per group pattern?

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) rn
  FROM events
) WHERE rn = 1;

Some engines support; cleaner: chain in CTEs. Combine jsonb_array_elements + window — usually one row per element. EXP(SUM(LN(1 + rate)) OVER (ORDER BY ts)) - 1

Question 18 SQL Window Functions Cheatsheet

Recursive CTE vs window — when?

ROW_NUMBER: unique sequential.
RANK: same value → same rank, leaves gaps.
DENSE_RANK: same value → same rank, no gaps. Recursive when each step depends on previous COMPUTED step; window for direct row-relative. EXP(SUM(LN(1 + rate)) OVER (ORDER BY ts)) - 1 GROUP BY signup_month, period_index; compute retention as COUNT(distinct user) / first-period count.

Question 19 SQL Window Functions Cheatsheet

Window function with array_agg + ordering?

EXP(SUM(LN(1 + rate)) OVER (ORDER BY ts)) - 1 A function computed across a set of rows related to the current row, without collapsing the result set. 30 rows back through the row just before current — excludes current row. ARRAY_AGG(x ORDER BY ts) OVER (PARTITION BY g) — ordered array per partition.

Question 20 SQL Window Functions Cheatsheet

MySQL window function support?

Treats peer groups (ties) as units — useful for "include all ties in window". Available from MySQL 8.0; earlier requires emulation with user variables or self-joins. Defines the "before" and "after" of the frame — required for cumulative, running, lag/lead semantics. Wrap UNION in subquery so window sees the combined rows.

Question 21 SQL Window Functions Cheatsheet

Subtle frame difference between OVER (ORDER BY x) and OVER ()?

LAG(x, 7) — 3rd arg is default for missing rows. Available from SQLite 3.25 (2018) — supports most standard windows. With ORDER BY, default frame is RANGE UNBOUNDED PRECEDING → CURRENT ROW. Without ORDER BY, the whole partition is the frame (good for percent-of-total). COUNT(*) counts rows; COUNT(col) counts non-null values.

Question 22 SQL Window Functions Cheatsheet

EXPLAIN window function operator?

"WindowAgg" in PostgreSQL, "Window" in SQL Server, "Window" in BigQuery — appears after the sort. When there is no previous row in the partition and no default supplied. WITH x AS MATERIALIZED (...) forces materialization; can change performance. Use a CTE; many planners deduplicate.

Question 23 SQL Window Functions Cheatsheet

Multiple window functions sharing window?

Define once with WINDOW clause: WINDOW w AS (PARTITION BY g ORDER BY ts); reference SUM(x) OVER w. SELECT generate_series('2026-01-01'::date, '2026-12-31'::date, '1 day'::interval)::date AS day NTILE(4) OVER (ORDER BY score) — quartiles. Distributes rows into N buckets as evenly as possible — returns bucket number 1..N.

Question 24 SQL Window Functions Cheatsheet

Compute z-score per group?

(x - AVG(x) OVER (PARTITION BY g)) / NULLIF(STDDEV(x) OVER (PARTITION BY g), 0)

SELECT * FROM t1 a, LATERAL (SELECT * FROM t1 b WHERE b.ts < a.ts AND b.user_id = a.user_id ORDER BY b.ts DESC LIMIT 1) b;

SUM(x) OVER (PARTITION BY g ORDER BY ts) * 100.0 / SUM(x) OVER (PARTITION BY g) Often forces materialization; window first in CTE, then join can be faster.

Question 25 SQL Window Functions Cheatsheet

Regression slope/intercept?

SUM(x) FILTER (WHERE active) OVER (PARTITION BY g) — conditional aggregation. REGR_SLOPE(y, x) and REGR_INTERCEPT(y, x) — linear regression aggregates. Common pattern in mart layer for ranking, deduping, retention metrics. Window functions don't support DISTINCT. Workaround: window over a derived helper (e.g., first-occurrence flag per user), then SUM.

Question 26 SQL Window Functions Cheatsheet

ClickHouse window functions?

Wrap UNION in subquery so window sees the combined rows. Supported since 21.x; some unique syntax for arrays and joinGet. ARRAY_AGG(x) OVER (PARTITION BY g) — collects partition values into an array per row. SUM(x) OVER (PARTITION BY g ORDER BY ts) * 100.0 / SUM(x) OVER (PARTITION BY g)

Question 27 SQL Window Functions Cheatsheet

Window for "yesterday's value"?

SUM(amount) OVER (PARTITION BY user_id ORDER BY ts ROWS UNBOUNDED PRECEDING)

Returns total row count per row, no GROUP BY needed — great for pagination metadata in a single query.

SUM(CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) > INTERVAL '30 min' THEN 1 ELSE 0 END)
OVER (PARTITION BY user_id ORDER BY ts) AS session_id

LAG(x, 1) OVER (ORDER BY day) — assumes one row per day; fill gaps with calendar table.

Question 28 SQL Window Functions Cheatsheet

Gaps and islands — basic technique?

ORDER BY ASC for first; DESC for last. Subtract a running counter from the value (or date) — items in the same "island" share a result. ROW_NUMBER() ≤ 3 — arbitrary tiebreak per ORDER BY.

SELECT value FROM (
  SELECT value, COUNT(*) c, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) r
  FROM t GROUP BY value
) x WHERE r = 1;

Question 29 SQL Window Functions Cheatsheet

Value window functions list?

MIN(ts) OVER (PARTITION BY g ORDER BY ts ROWS UNBOUNDED PRECEDING) LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE. Not stable across reads if ORDER BY has ties. Add tiebreaker (e.g., id) for deterministic pages. Defines logical order within the partition — only affects frame and ranking functions, not the outer result order.

Question 30 SQL Window Functions Cheatsheet

Why does LAG with no offset use 1?

Default n is 1 — the immediately previous row. Engine support varies; older MySQL, SQL Server lack INTERVAL frames. Use ROWS with pre-aggregated daily counts. Combine: GROUP BY ROLLUP for subtotals, then window over the result for cumulative breakdowns. LEAD(ts) OVER (PARTITION BY user ORDER BY ts) - ts < INTERVAL '7 days'

Question 31 SQL Window Functions Cheatsheet

GROUP BY ROLLUP combined with window?

SUM(x) FILTER (WHERE active) OVER (PARTITION BY g) — conditional aggregation. AVG(COALESCE(x, 0)) OVER (...) Window works on the result rows from GROUP BY — useful for cumulative subtotals. "WindowAgg" in PostgreSQL, "Window" in SQL Server, "Window" in BigQuery — appears after the sort.

Question 32 SQL Window Functions Cheatsheet

ARRAY_AGG over window?

ARRAY_AGG(x) OVER (PARTITION BY g) — collects partition values into an array per row. PIVOT (Snowflake/SQLServer) directly; without PIVOT, use CASE + GROUP BY + windows for cumulative columns. LAG(x, 1) OVER (ORDER BY day) — assumes one row per day; fill gaps with calendar table.

SELECT col, SUM(x) OVER w, AVG(x) OVER w
FROM t
WINDOW w AS (PARTITION BY g ORDER BY ts);

Question 33 SQL Window Functions Cheatsheet

Snowflake performance tip for windows?

ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) AS delta EXP(SUM(LN(1 + rate)) OVER (ORDER BY ts)) - 1 Cluster table on partition keys; reduces scan during window evaluation. AVG(x) OVER (ORDER BY ts ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)

Question 34 SQL Window Functions Cheatsheet

Cumulative average?

Available in PostgreSQL 16+, BigQuery, Snowflake, Redshift, SQL Server, Oracle. Not in MySQL until 8.0.22+. Window is often simpler for adjacent-row math; LATERAL needed when matching condition is complex (e.g., "nearest by timestamp from another table"). Specifies which rows within the partition the function operates on (e.g., ROWS BETWEEN 3 PRECEDING AND CURRENT ROW). AVG(x) OVER (ORDER BY ts ROWS UNBOUNDED PRECEDING)

Question 35 SQL Window Functions Cheatsheet

Why does plain LAST_VALUE seem wrong?

NTH_VALUE(col, 3) OVER (...) — 3rd value in the frame. Default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — LAST_VALUE returns the current row! Override the frame. WHERE filters base rows before window evaluation. Wrap in subquery / CTE / use QUALIFY.

SELECT value FROM (
  SELECT value, COUNT(*) c, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) r
  FROM t GROUP BY value
) x WHERE r = 1;

Question 36 SQL Window Functions Cheatsheet

Combining window aggregates with HAVING?

Rollup adds subtotals as new rows; windows keep all original rows and add columns. HAVING applies before window; use QUALIFY or wrap in subquery. First (rows mod N) buckets get an extra row.

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) rn
  FROM events
) WHERE rn = 1;

Question 37 SQL Window Functions Cheatsheet

Difference: cumulative SUM and SUM OVER ()?

SUM(x) OVER () = grand total per row. SUM(x) OVER (ORDER BY ts) = running total. SELECT DISTINCT ON (g) g, ts, x FROM t ORDER BY g, ts DESC — keeps first per g per ORDER BY. Compute Q1, Q3 with PERCENTILE_CONT; mark rows outside Q1 - 1.5*IQR or Q3 + 1.5*IQR. WIDTH_BUCKET assigns by value bins (equi-width); NTILE assigns by row count (equi-count).

Question 38 SQL Window Functions Cheatsheet

Naming results from window for outer query?

Window functions don't support DISTINCT. Workaround: window over a derived helper (e.g., first-occurrence flag per user), then SUM. Use alias: SELECT ROW_NUMBER() OVER (...) AS rn FROM ... then filter on rn in subquery. Results non-deterministic between runs; always include id or sequence as final tiebreak. Composite on (partition_col, order_col) — eliminates a sort.

Question 39 SQL Window Functions Cheatsheet

Window function with named window clause?

Snowflake window: RATIO_TO_REPORT(x) OVER (PARTITION BY g) = x / SUM(x) OVER (PARTITION BY g). Helpful when window result is reused: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (...) rn FROM t) SELECT * FROM ranked WHERE rn = 1;

SELECT col, SUM(x) OVER w, AVG(x) OVER w
FROM t
WINDOW w AS (PARTITION BY g ORDER BY ts);

ts - MIN(ts) OVER (PARTITION BY user_id)

Question 40 SQL Window Functions Cheatsheet

LISTAGG / STRING_AGG with window?

LEAD(ts) OVER (PARTITION BY user ORDER BY ts) - ts < INTERVAL '7 days' Snowflake LISTAGG and PostgreSQL STRING_AGG support OVER — concatenate strings while keeping rows. Cumulative distribution — fraction of rows with values ≤ current row's value (0..1]. Two items share rank 1; the next item gets rank 3 (gap). Use DENSE_RANK if you want 1, 1, 2.

Question 41 SQL Window Functions Cheatsheet

Window with self-join — when needed?

When you need a function not expressible by window (e.g., compare each row to top-3 average from another table). On large partitions, sort + window may spill — increase work_mem (PG) or use smaller partitions. Recursive CTE required; window alone can't express EMA exactly. CORR(x, y) OVER (PARTITION BY g) — available in PostgreSQL, BigQuery.

Question 42 SQL Window Functions Cheatsheet

Lag over a partition by composite key?

NTILE(10) OVER (PARTITION BY g ORDER BY x) — decile per row. LAG(x) OVER (PARTITION BY country, city ORDER BY ts) amount - LAG(amount, 12) OVER (PARTITION BY metric ORDER BY month)

SUM(CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) > INTERVAL '30 min' THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY ts) AS session_id

Question 43 SQL Window Functions Cheatsheet

Window over GROUPED data?

Aggregate first, then window — separate steps for readability. QUALIFY MAX(x) OVER (PARTITION BY g) = x — keeps max-per-group rows. LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE. EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, EXCLUDE NO OTHERS — fine-grained frame control.

Question 44 SQL Window Functions Cheatsheet

Difference between ROW_NUMBER, RANK, DENSE_RANK?

(price - LAG(price) OVER (ORDER BY ts)) * 100.0 / LAG(price) OVER (ORDER BY ts) Composite on (partition_col, order_col) — eliminates a sort. Allowed; refresh strategy depends on data growth. Useful for cumulative metrics. ROW_NUMBER: unique sequential.
RANK: same value → same rank, leaves gaps.
DENSE_RANK: same value → same rank, no gaps.

Question 45 SQL Window Functions Cheatsheet

Frame BETWEEN N PRECEDING AND N FOLLOWING?

Not stable across reads if ORDER BY has ties. Add tiebreaker (e.g., id) for deterministic pages. Centered window of 2N+1 rows around the current row. (x - AVG(x) OVER w) / STDDEV(x) OVER w

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) rn
  FROM events
) WHERE rn = 1;

Question 46 SQL Window Functions Cheatsheet

COUNT(*) vs COUNT(col) in window?

With ORDER BY, default frame is RANGE UNBOUNDED PRECEDING → CURRENT ROW. Without ORDER BY, the whole partition is the frame (good for percent-of-total). COUNT(*) counts rows; COUNT(col) counts non-null values. Compute Q1, Q3 with PERCENTILE_CONT; mark rows outside Q1 - 1.5*IQR or Q3 + 1.5*IQR. SUM(x) FILTER (WHERE active) OVER (PARTITION BY g) — conditional aggregation.

Question 47 SQL Window Functions Cheatsheet

Can you use window functions in WHERE?

WHERE name = 'X' QUALIFY ROW_NUMBER() OVER (PARTITION BY g ORDER BY t) = 1 — no nesting. Treats peer groups (ties) as units — useful for "include all ties in window". The entire partition (all rows). No — windows are computed after WHERE. Wrap in a subquery/CTE.

Question 48 SQL Window Functions Cheatsheet

Median (50th percentile)?

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) OVER (PARTITION BY g) (Postgres/BigQuery). LAG(x) OVER (PARTITION BY country, city ORDER BY ts) ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts) = 1 Specifies which rows within the partition the function operates on (e.g., ROWS BETWEEN 3 PRECEDING AND CURRENT ROW).

Question 49 SQL Window Functions Cheatsheet

Compute compound growth?

EXP(SUM(LN(1 + rate)) OVER (ORDER BY ts)) - 1 SELECT day FROM UNNEST(GENERATE_DATE_ARRAY('2026-01-01','2026-12-31')) day AVG(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) DISTINCT ON (g) ... ORDER BY g, ts DESC picks first per g — concise PG-specific shortcut.

Question 50 SQL Window Functions Cheatsheet

Common gotcha with ORDER BY in OVER vs query ORDER BY?

Postgres can use index to avoid Sort step — explain plan shows "WindowAgg" without "Sort". They're independent. OVER's ORDER BY only orders the frame. The outer ORDER BY orders results. Pair LAG(x,1), LAG(x,2), LAG(x,3) and compute diffs — useful for spike detection. Some engines support; cleaner: chain in CTEs.