Title: SQL Window Functions Cheatsheet — LearnCoachAssist

Q: Running total of amount by user_id ordered by ts?
A: SUM(amount) OVER (PARTITION BY user_id ORDER BY ts ROWS UNBOUNDED PRECEDING)

Q: Difference between ROW_NUMBER, RANK, DENSE_RANK?
A: ROW_NUMBER: unique sequential.
RANK: same value → same rank, leaves gaps.
DENSE_RANK: same value → same rank, no gaps.

Q: Top N rows per group?
A: SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY score DESC) rn
  FROM t
) x WHERE rn <= 3;

Q: Previous row's value in same partition?
A: LAG(price) OVER (PARTITION BY symbol ORDER BY ts)

Q: Next row's value?
A: LEAD(price) OVER (PARTITION BY symbol ORDER BY ts)

Q: Percentage change from previous row?
A: (price - LAG(price) OVER (ORDER BY ts)) * 100.0 / LAG(price) OVER (ORDER BY ts)

Q: Cumulative average?
A: AVG(x) OVER (ORDER BY ts ROWS UNBOUNDED PRECEDING)

Q: Moving 7-day average?
A: AVG(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Q: Rank within partition by score?
A: RANK() OVER (PARTITION BY category ORDER BY score DESC)

Q: First and last value per partition?
A: 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)

Q: Why does plain LAST_VALUE seem wrong?
A: Default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — LAST_VALUE returns the current row! Override the frame.

Q: Percentile rank?
A: PERCENT_RANK() OVER (ORDER BY score) — value 0..1, percentile within partition.

Q: Bucket rows into N equal groups?
A: NTILE(4) OVER (ORDER BY score) — quartiles.

Q: Difference: aggregate + GROUP BY vs window aggregate?
A: GROUP BY: collapses rows.
Window: keeps each row, adds aggregated column alongside.

Q: Why is COUNT(*) OVER () useful?
A: Returns total row count per row, no GROUP BY needed — great for pagination metadata in a single query.

Q: Window frame default for cumulative SUM with ORDER BY?
A: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (running total).

Q: Difference: ROWS vs RANGE in frame?
A: ROWS: physical row offsets.
RANGE: based on the ORDER BY value (treats duplicates as the same logical position).

Q: Window function with named window clause?
A: SELECT col, SUM(x) OVER w, AVG(x) OVER w
FROM t
WINDOW w AS (PARTITION BY g ORDER BY ts);

Q: Can you use window functions in WHERE?
A: No — windows are computed after WHERE. Wrap in a subquery/CTE.

Q: Common gotcha with ORDER BY in OVER vs query ORDER BY?
A: They're independent. OVER's ORDER BY only orders the frame. The outer ORDER BY orders results.

Q: Find first signup per cohort month?
A: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('month', signup_at) ORDER BY signup_at) rn FROM users) x WHERE rn = 1;

Q: Detect gaps in a sequence (islands & gaps)?
A: Use row_number() - lag(value) trick or compare LAG(date_col).

Q: Days since previous order per customer?
A: DATE_DIFF(order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date), DAY)

Q: Z-score within partition?
A: (x - AVG(x) OVER w) / STDDEV(x) OVER w

Q: Median (50th percentile)?
A: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) OVER (PARTITION BY g) (Postgres/BigQuery).

Q: Common window function performance tip?
A: Wrap into a CTE that filters early; window over only what's needed; ensure index supports PARTITION + ORDER columns.

Q: Window over join result vs over base table — performance?
A: Windows over joined data force materialization; sometimes prefer windowing first, then joining.

Q: Frame: BETWEEN 30 PRECEDING AND 1 PRECEDING — meaning?
A: 30 rows back through the row just before current — excludes current row.

Q: Cumulative distinct count workaround?
A: Window functions don't support DISTINCT. Workaround: window over a derived helper (e.g., first-occurrence flag per user), then SUM.

Q: Running max per partition?
A: MAX(x) OVER (PARTITION BY g ORDER BY ts ROWS UNBOUNDED PRECEDING)

Q: Find sessions by 30-min gap?
A: 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

Q: Compute mode (most frequent) using windows?
A: SELECT value FROM (
  SELECT value, COUNT(*) c, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) r
  FROM t GROUP BY value
) x WHERE r = 1;

Q: Calculate percent of total per row?
A: x * 100.0 / SUM(x) OVER ()

Q: Why does a tied score on RANK produce 1, 1, 3?
A: Two items share rank 1; the next item gets rank 3 (gap). Use DENSE_RANK if you want 1, 1, 2.

Q: Lag with N rows back?
A: LAG(x, 7) — 3rd arg is default for missing rows.

Q: Subtle frame difference between OVER (ORDER BY x) and OVER ()?
A: 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).

Q: What is a window function?
A: A function computed across a set of rows related to the current row, without collapsing the result set.

Q: General window function syntax?
A: function() OVER (PARTITION BY ... ORDER BY ... frame_clause)

Q: What is a PARTITION BY clause?
A: Divides rows into groups; the window function applies independently within each group.

Q: What is the ORDER BY in OVER?
A: Defines logical order within the partition — only affects frame and ranking functions, not the outer result order.

Q: What is a frame clause?
A: Specifies which rows within the partition the function operates on (e.g., ROWS BETWEEN 3 PRECEDING AND CURRENT ROW).

Q: Default frame with ORDER BY?
A: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Q: Default frame without ORDER BY?
A: The entire partition (all rows).

Q: Aggregate window function?
A: Standard aggregates (SUM, AVG, COUNT, MIN, MAX) used in OVER — return one value per row.

Q: Ranking window functions list?
A: ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST.

Q: Value window functions list?
A: LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE.

Q: What does NTILE(N) do?
A: Distributes rows into N buckets as evenly as possible — returns bucket number 1..N.

Q: CUME_DIST description?
A: Cumulative distribution — fraction of rows with values ≤ current row's value (0..1].

Q: PERCENT_RANK description?
A: (rank - 1) / (rows - 1) — relative position within partition (0..1).

Q: NTH_VALUE syntax?
A: NTH_VALUE(col, 3) OVER (...) — 3rd value in the frame.

Q: LAG(col, n, default) signature?
A: LAG(col, n_rows_back, default_if_null)

Q: LEAD(col, n, default) signature?
A: LEAD(col, n_rows_forward, default_if_null)

Q: Why does LAG with no offset use 1?
A: Default n is 1 — the immediately previous row.

Q: What returns NULL with LAG?
A: When there is no previous row in the partition and no default supplied.

Q: ROWS vs RANGE — when matters?
A: With ORDER BY containing ties, RANGE includes all peer rows; ROWS uses physical row positions.

Q: GROUPS frame clause?
A: Treats peer groups (ties) as units — useful for "include all ties in window".

Q: Frame BETWEEN N PRECEDING AND N FOLLOWING?
A: Centered window of 2N+1 rows around the current row.

Q: Frame BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING?
A: Entire partition.

Q: Compute running total query?
A: SELECT amount, SUM(amount) OVER (ORDER BY ts) AS running FROM t;

Q: Compute moving average (5 rows)?
A: AVG(x) OVER (ORDER BY ts ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)

Q: Find last non-null value (carry forward)?
A: LAST_VALUE(x IGNORE NULLS) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Q: IGNORE NULLS support?
A: Available in PostgreSQL 16+, BigQuery, Snowflake, Redshift, SQL Server, Oracle. Not in MySQL until 8.0.22+.

Q: RESPECT NULLS vs IGNORE NULLS?
A: RESPECT NULLS (default) treats NULL as a valid value; IGNORE NULLS skips NULL when picking value.

Q: Percent of group total?
A: x * 100.0 / SUM(x) OVER (PARTITION BY group_id)

Q: Cumulative percent of partition total?
A: SUM(x) OVER (PARTITION BY g ORDER BY ts) * 100.0 / SUM(x) OVER (PARTITION BY g)

Q: Year-over-year change?
A: amount - LAG(amount, 12) OVER (PARTITION BY metric ORDER BY month)

Q: Identify duplicates with ROW_NUMBER?
A: ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn — filter rn > 1.

Q: Keep latest per group pattern?
A: SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) rn
  FROM events
) WHERE rn = 1;

Q: Top 3 per group (with ties)?
A: Use RANK() or DENSE_RANK() and filter rank ≤ 3 — includes tied items.

Q: Top 3 per group (no ties)?
A: ROW_NUMBER() ≤ 3 — arbitrary tiebreak per ORDER BY.

Q: Difference: cumulative SUM and SUM OVER ()?
A: SUM(x) OVER () = grand total per row. SUM(x) OVER (ORDER BY ts) = running total.

Q: Window function in subquery vs CTE — perf?
A: Usually equivalent — planners often inline. CTE more readable.

Q: Why can't window function appear in WHERE?
A: WHERE filters base rows before window evaluation. Wrap in subquery / CTE / use QUALIFY.

Q: QUALIFY clause?
A: Snowflake/BigQuery shortcut to filter on window functions: QUALIFY ROW_NUMBER() OVER (...) = 1

Q: Window function in GROUP BY?
A: Allowed in SELECT but the column inside must appear in GROUP BY or be aggregated — windowing happens after grouping.

Q: Find users whose first event was login?
A: FIRST_VALUE(event_type) OVER (PARTITION BY user_id ORDER BY ts) then filter for 'login'.

Q: Difference between two consecutive timestamps?
A: ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) AS delta

Q: Sessionize by 30-min inactivity gap?
A: 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

Q: Gaps and islands — basic technique?
A: Subtract a running counter from the value (or date) — items in the same "island" share a result.

Q: Islands example?
A: day - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) * INTERVAL '1 day' — same value = same streak.

Q: Longest streak per user?
A: GROUP BY user, island_id; MAX(COUNT(*)) per user.

Q: Detect new vs returning users?
A: First event vs not-first via ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts) = 1

Q: Compute rolling unique users?
A: Windowing doesn't support DISTINCT natively — use a per-user first-occurrence flag, then SUM over window.

Q: First-occurrence flag pattern?
A: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts) = 1 — boolean treated as 1/0.

Q: Find median per group?
A: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) OVER (PARTITION BY g)

Q: Find mode per group?
A: Subquery counting per value + ROW_NUMBER() ranked by count.

Q: Window function on join result?
A: Often forces materialization; window first in CTE, then join can be faster.

Q: DENSE_RANK use case?
A: When you want consecutive ranks ignoring ties (1,2,2,3) instead of (1,2,2,4).

Q: Window function with HAVING?
A: HAVING applies to grouping; window functions go in SELECT and outer WHERE/QUALIFY.

Q: Why window function ORDER BY matters?
A: Defines the "before" and "after" of the frame — required for cumulative, running, lag/lead semantics.

Q: Window function ORDER BY with NULLS?
A: Append NULLS FIRST or NULLS LAST to control NULL position.

Q: Multiple window functions sharing window?
A: Define once with WINDOW clause: WINDOW w AS (PARTITION BY g ORDER BY ts); reference SUM(x) OVER w.

Q: ROW_NUMBER for pagination — caveat?
A: Not stable across reads if ORDER BY has ties. Add tiebreaker (e.g., id) for deterministic pages.

Q: Keyset pagination vs OFFSET — why prefer?
A: OFFSET scans skipped rows; keyset (WHERE id > last_seen) uses index — O(1) per page.

Q: ROW_NUMBER vs LIMIT?
A: LIMIT for top-N globally; ROW_NUMBER for top-N per group.

Q: COUNT(*) OVER () for total rows in same query?
A: Saves a separate query; planners often compute once.

Q: GROUP BY ROLLUP combined with window?
A: Window works on the result rows from GROUP BY — useful for cumulative subtotals.

Q: Difference: rolling sum vs running total?
A: Same concept, different phrasing — both refer to cumulative aggregation over a frame.

Q: Frame: ROWS BETWEEN 6 PRECEDING AND 0 PRECEDING?
A: 7-row window including current — equivalent to ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.

Q: Window for "yesterday's value"?
A: LAG(x, 1) OVER (ORDER BY day) — assumes one row per day; fill gaps with calendar table.

Q: Calendar table technique?
A: Generate dates, LEFT JOIN data, then LAG over the calendar — fills missing days.

Q: generate_series for calendar in Postgres?
A: SELECT generate_series('2026-01-01'::date, '2026-12-31'::date, '1 day'::interval)::date AS day

Q: BigQuery calendar?
A: SELECT day FROM UNNEST(GENERATE_DATE_ARRAY('2026-01-01','2026-12-31')) day

Q: Snowflake calendar?
A: SELECT DATEADD(day, SEQ4(), '2026-01-01') AS day FROM TABLE(GENERATOR(ROWCOUNT => 366))

Q: Compute compound growth?
A: EXP(SUM(LN(1 + rate)) OVER (ORDER BY ts)) - 1

Q: Days-since-last-purchase per user?
A: EXTRACT(DAY FROM ts - LAG(ts) OVER (PARTITION BY user ORDER BY ts))

Q: Days-to-next-purchase per user?
A: EXTRACT(DAY FROM LEAD(ts) OVER (PARTITION BY user ORDER BY ts) - ts)

Q: Cohort analysis basic query?
A: GROUP BY signup_month, period_index; compute retention as COUNT(distinct user) / first-period count.

Q: Cohort retention with window?
A: COUNT(DISTINCT user_id) OVER (PARTITION BY cohort, period) * 1.0 / FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (PARTITION BY cohort ORDER BY period)

Q: Rolling 28-day active users?
A: COUNT(DISTINCT user_id) OVER (ORDER BY day RANGE BETWEEN INTERVAL '27 days' PRECEDING AND CURRENT ROW) — RANGE intervals supported in PG, BQ, etc.

Q: Why might RANGE INTERVAL fail?
A: Engine support varies; older MySQL, SQL Server lack INTERVAL frames. Use ROWS with pre-aggregated daily counts.

Q: Rank brands by sales within each category?
A: RANK() OVER (PARTITION BY category ORDER BY sales DESC)

Q: Compute z-score per group?
A: (x - AVG(x) OVER (PARTITION BY g)) / NULLIF(STDDEV(x) OVER (PARTITION BY g), 0)

Q: Compute coefficient of variation per group?
A: STDDEV(x) OVER (PARTITION BY g) / NULLIF(AVG(x) OVER (PARTITION BY g), 0)

Q: Compute z-score using sample stddev?
A: Use STDDEV_SAMP (default); use STDDEV_POP for population.

Q: Variance/stddev window functions?
A: VAR_SAMP, VAR_POP, STDDEV_SAMP, STDDEV_POP — usable as window functions.

Q: Correlation in SQL?
A: CORR(x, y) OVER (PARTITION BY g) — available in PostgreSQL, BigQuery.

Q: Regression slope/intercept?
A: REGR_SLOPE(y, x) and REGR_INTERCEPT(y, x) — linear regression aggregates.

Q: Window vs aggregate of regression?
A: Both forms exist — REGR_* with OVER returns slope per partition without collapsing rows.

Q: LISTAGG / STRING_AGG with window?
A: Snowflake LISTAGG and PostgreSQL STRING_AGG support OVER — concatenate strings while keeping rows.

Q: ARRAY_AGG over window?
A: ARRAY_AGG(x) OVER (PARTITION BY g) — collects partition values into an array per row.

Q: Histogram per group via NTILE?
A: NTILE(10) OVER (PARTITION BY g ORDER BY x) — decile per row.

Q: WIDTH_BUCKET vs NTILE?
A: WIDTH_BUCKET assigns by value bins (equi-width); NTILE assigns by row count (equi-count).

Q: Detect outliers via IQR?
A: Compute Q1, Q3 with PERCENTILE_CONT; mark rows outside Q1 - 1.5*IQR or Q3 + 1.5*IQR.

Q: First/last per group without window?
A: GROUP BY + MIN/MAX of ts; then JOIN — works but window often cleaner.

Q: Difference: SELECT DISTINCT ON vs ROW_NUMBER (PG)?
A: DISTINCT ON (g) ... ORDER BY g, ts DESC picks first per g — concise PG-specific shortcut.

Q: keep-first vs keep-last pattern?
A: ORDER BY ASC for first; DESC for last.

Q: QUALIFY with multiple windows?
A: QUALIFY ROW_NUMBER() OVER (...) = 1 AND RANK() OVER (...) <= 3

Q: QUALIFY availability?
A: Snowflake, BigQuery, Databricks, Teradata. Not Postgres, MySQL, SQL Server natively.

Q: Window with FILTER clause (PG/SQL standard)?
A: SUM(x) FILTER (WHERE active) OVER (PARTITION BY g) — conditional aggregation.

Q: FILTER vs CASE in aggregate?
A: FILTER cleaner; CASE works on engines without FILTER (most do now).

Q: Window function over UNION ALL?
A: Wrap UNION in subquery; window applies to combined result.

Q: Top N percent per group?
A: NTILE(100) + filter ≤ N, or RANK over count-aware quantile.

Q: Bottom N per group?
A: ROW_NUMBER ORDER BY ASC, filter ≤ N.

Q: Tied ranking handling — when matters?
A: When ties should share a position (RANK) vs each get a unique sequence (ROW_NUMBER).

Q: Implementation note: ORDER BY with non-deterministic order?
A: Adding ROW_NUMBER without unique ORDER BY can shuffle equal rows between runs. Always include tiebreaker.

Q: Compute median absolute deviation?
A: Use PERCENTILE_CONT to compute median, then median of absolute differences.

Q: Compute exponential moving average?
A: Recursive CTE required; window alone can't express EMA exactly.

Q: EMA recursive CTE shape?
A: WITH RECURSIVE e AS (
  SELECT ts, x, x::float AS ema FROM t WHERE ts = (SELECT MIN(ts) FROM t)
  UNION ALL
  SELECT t.ts, t.x, 0.3*t.x + 0.7*e.ema
  FROM t JOIN e ON t.ts = e.ts + INTERVAL '1 day'
)
SELECT * FROM e;

Q: Lag over a partition by composite key?
A: LAG(x) OVER (PARTITION BY country, city ORDER BY ts)

Q: Reuse a sub-expression in many windows?
A: Use a CTE; many planners deduplicate.

Q: Index that helps window function?
A: Composite on (partition_col, order_col) — eliminates a sort.

Q: Snowflake performance tip for windows?
A: Cluster table on partition keys; reduces scan during window evaluation.

Q: BigQuery performance tip?
A: Partition the table by date and cluster by partition columns used in windows.

Q: Window vs GROUP BY ROLLUP?
A: Rollup adds subtotals as new rows; windows keep all original rows and add columns.

Q: ROLLUP with windows?
A: Combine: GROUP BY ROLLUP for subtotals, then window over the result for cumulative breakdowns.

Q: PIVOT vs window for wide-to-long?
A: PIVOT (Snowflake/SQLServer) directly; without PIVOT, use CASE + GROUP BY + windows for cumulative columns.

Q: Window in INSERT/UPDATE?
A: Allowed in SELECT subqueries within DML — useful for backfills.

Q: UPDATE rows using window function (Postgres)?
A: 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;

Q: Delete duplicates keeping earliest?
A: DELETE FROM t USING (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) rn FROM t) x
WHERE t.id = x.id AND x.rn > 1;

Q: Materialized view with window function?
A: Allowed; refresh strategy depends on data growth. Useful for cumulative metrics.

Q: Recursive CTE vs window — when?
A: Recursive when each step depends on previous COMPUTED step; window for direct row-relative.

Q: Window function CTE materialization in PG 12+?
A: WITH x AS MATERIALIZED (...) forces materialization; can change performance.

Q: Sliding range frame with multiple intervals?
A: Not supported directly — split into multiple windows or use joins on shifted timestamps.

Q: Frame with EXCLUDE clause?
A: EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, EXCLUDE NO OTHERS — fine-grained frame control.

Q: EXCLUDE CURRENT ROW use case?
A: Compute average of peers without including self.

Q: Window with offset of variable rows?
A: LAG/LEAD take a constant offset; for variable, use self-join or LATERAL.

Q: Lateral join + ORDER BY for "previous row matching condition"?
A: 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;

Q: Use window in WHERE via QUALIFY (Snowflake)?
A: QUALIFY MAX(x) OVER (PARTITION BY g) = x — keeps max-per-group rows.

Q: PostgreSQL "DISTINCT ON" idiom?
A: SELECT DISTINCT ON (g) g, ts, x FROM t ORDER BY g, ts DESC — keeps first per g per ORDER BY.

Q: MySQL window function support?
A: Available from MySQL 8.0; earlier requires emulation with user variables or self-joins.

Q: SQLite window function support?
A: Available from SQLite 3.25 (2018) — supports most standard windows.

Q: SQL Server PERCENTILE_CONT — caveat?
A: Implemented as window function only — requires OVER even for whole-table median.

Q: RANGE INTERVAL frames in BigQuery?
A: BigQuery supports RANGE with date/time intervals — useful for rolling time-based metrics.

Q: DuckDB window support?
A: Full support including QUALIFY and RANGE intervals — closer to BigQuery/Snowflake feature set.

Q: ClickHouse window functions?
A: Supported since 21.x; some unique syntax for arrays and joinGet.

Q: Snowflake QUALIFY simplification?
A: WHERE name = 'X' QUALIFY ROW_NUMBER() OVER (PARTITION BY g ORDER BY t) = 1 — no nesting.

Q: Hot tip: avoid SELECT *
A: With windows in joins, explicitly list columns — easier to debug and fewer surprises.

Q: Compute "first time user did X" date per user?
A: MIN(ts) OVER (PARTITION BY user_id) AS first_action

Q: "Time since first action" per row?
A: ts - MIN(ts) OVER (PARTITION BY user_id)

Q: Compare each row to partition average?
A: x - AVG(x) OVER (PARTITION BY g)

Q: Compare each row to group min/max?
A: x - MIN(x) OVER (PARTITION BY g) or MAX(x) OVER (PARTITION BY g) - x

Q: Identify rank within sliding window?
A: Combine RANK + ROWS BETWEEN frame — niche use.

Q: Compute next 3 rows sum?
A: SUM(x) OVER (ORDER BY ts ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING)

Q: Time-weighted average (TWA)?
A: Combine LAG(ts) for interval lengths * value, summed; non-trivial in plain SQL.

Q: Window with FILTER for conditional running counts?
A: COUNT(*) FILTER (WHERE status='ok') OVER (PARTITION BY user ORDER BY ts ROWS UNBOUNDED PRECEDING)

Q: Why prefer FILTER over CASE in window?
A: Optimizer can sometimes prune; reads cleaner.

Q: Window function ORDER BY referencing computed column?
A: Must reference column expression directly — alias from SELECT not usable inside OVER ORDER BY in some engines.

Q: Naming results from window for outer query?
A: Use alias: SELECT ROW_NUMBER() OVER (...) AS rn FROM ... then filter on rn in subquery.

Q: Calculate "n_users with score > me"?
A: 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)

Q: Calculate "share of cumulative top X%"?
A: Use NTILE or PERCENT_RANK to bucket; SUM share per bucket.

Q: Window over GROUPED data?
A: Aggregate first, then window — separate steps for readability.

Q: Window functions vs SELF JOIN performance?
A: Windows usually win for large datasets — single pass vs N×N joins.

Q: Use case: detect "first ever" purchase per user globally?
A: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts) = 1

Q: Use case: detect repeat purchases within 7 days?
A: LEAD(ts) OVER (PARTITION BY user ORDER BY ts) - ts < INTERVAL '7 days'

Q: Use case: detect "churned" users (last activity > 30 days ago)?
A: Compute max(ts) per user, filter where now() - max(ts) > 30 days.

Q: Window function vs LATERAL JOIN?
A: Window is often simpler for adjacent-row math; LATERAL needed when matching condition is complex (e.g., "nearest by timestamp from another table").

Q: Performance: window often spills to disk?
A: On large partitions, sort + window may spill — increase work_mem (PG) or use smaller partitions.

Q: SQL Server NTILE with rows count not divisible?
A: First (rows mod N) buckets get an extra row.

Q: Window with empty frame returns?
A: NULL (or 0 for COUNT) — when frame is empty, aggregates return their identity element.

Q: COUNT(*) vs COUNT(col) in window?
A: COUNT(*) counts rows; COUNT(col) counts non-null values.

Q: Compute "1st, 2nd, 3rd value differences"?
A: Pair LAG(x,1), LAG(x,2), LAG(x,3) and compute diffs — useful for spike detection.

Q: Use case: detect price spike (3-sigma)?
A: Compare current value to AVG ± 3*STDDEV over a trailing window.

Q: Performance: window function on indexed sort?
A: Postgres can use index to avoid Sort step — explain plan shows "WindowAgg" without "Sort".

Q: EXPLAIN window function operator?
A: "WindowAgg" in PostgreSQL, "Window" in SQL Server, "Window" in BigQuery — appears after the sort.

Q: Combining window aggregates with HAVING?
A: HAVING applies before window; use QUALIFY or wrap in subquery.

Q: Window on a CASE result?
A: SUM(CASE WHEN x > 0 THEN x ELSE 0 END) OVER (...)

Q: Window on COALESCE?
A: AVG(COALESCE(x, 0)) OVER (...)

Q: Window over a UNION result?
A: Wrap UNION in subquery so window sees the combined rows.

Q: Window with self-join — when needed?
A: When you need a function not expressible by window (e.g., compare each row to top-3 average from another table).

Q: PostgreSQL window function on JSONB?
A: Cast or use json operators inside aggregate: SUM((data->>'amount')::numeric) OVER (...)

Q: Aggregating window over JSON array?
A: Combine jsonb_array_elements + window — usually one row per element.

Q: Window function in DBT models?
A: Common pattern in mart layer for ranking, deduping, retention metrics.

Q: Standard window function in Spark SQL?
A: Same syntax; uses Catalyst optimizer; requires partition for parallelism.

Q: SparkSQL: rangeBetween in Window API?
A: Window.partitionBy('g').orderBy('ts').rangeBetween(Window.unboundedPreceding, 0)

Q: Pandas equivalent of window?
A: df.groupby('g')['x'].cumsum() for running sum; rolling() for trailing window.

Q: SQL window vs Pandas — semantics?
A: Mostly aligned; Pandas defaults vary (e.g., rolling().mean() requires explicit min_periods).

Q: Use a CTE to materialize window results once?
A: Helpful when window result is reused: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (...) rn FROM t) SELECT * FROM ranked WHERE rn = 1;

Q: SELECT DISTINCT with window — combined behavior?
A: DISTINCT runs after window — may dedupe rows that differ only in window value if you SELECT only base columns.

Q: Use case: backfilling NULLs with previous value?
A: FIRST_VALUE(x IGNORE NULLS) OVER (PARTITION BY g ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Q: Use case: forward-fill with last_value style?
A: Equivalent — pick whichever IGNORE NULLS variant your engine supports.

Q: Window function on a window function?
A: Some engines support; cleaner: chain in CTEs.

Q: Window function with array_agg + ordering?
A: ARRAY_AGG(x ORDER BY ts) OVER (PARTITION BY g) — ordered array per partition.

Q: Common mistake: forgetting tiebreaker in ORDER BY?
A: Results non-deterministic between runs; always include id or sequence as final tiebreak.

Q: Common mistake: window over UNION without subquery?
A: Parser errors or unexpected scope — wrap in subquery.

Q: Common mistake: using window in WHERE?
A: Not allowed; refactor with CTE/subquery/QUALIFY.

Q: Snowflake-specific: WIDTH_BUCKET?
A: Same as standard — but Snowflake has many helpers like RATIO_TO_REPORT for percent-of-total.

Q: RATIO_TO_REPORT?
A: Snowflake window: RATIO_TO_REPORT(x) OVER (PARTITION BY g) = x / SUM(x) OVER (PARTITION BY g).

Q: Compute "running max date per group"?
A: MAX(ts) OVER (PARTITION BY g ORDER BY ts ROWS UNBOUNDED PRECEDING)

Q: Compute "running min date per group"?
A: MIN(ts) OVER (PARTITION BY g ORDER BY ts ROWS UNBOUNDED PRECEDING)

Q: Window function memory considerations?
A: Each partition must fit in memory for sort + buffer; large partitions stress work_mem.

Q: Window function and parallel execution?
A: Most engines parallelize within partition; small partitions limit parallelism.

Q: Tip for huge ROW_NUMBER tasks?
A: Pre-filter, pre-aggregate, or compute incrementally; full-table windows are expensive.