Advanced SQL Window Functions Questions

Mastery of Structured Query Language window functions and advanced aggregation techniques for analytical queries. Core function families include ranking functions such as ROW_NUMBER, RANK, DENSE_RANK, and NTILE; offset functions such as LAG and LEAD; value functions such as FIRST_VALUE, LAST_VALUE, and NTH_VALUE; and aggregate window expressions such as SUM OVER and AVG OVER. Candidates should understand the OVER clause with PARTITION BY and ORDER BY, frame specifications using ROWS BETWEEN and RANGE BETWEEN, tie handling, null behavior, and how frame definitions affect results. Common application patterns include top N per group, deduplication using row numbering, running totals and cumulative aggregates, moving averages, percent rank and distribution calculations, event sequencing and period over period comparisons, gap and island analysis, cohort and retention analysis, and trend and growth calculations. The topic also covers structuring complex queries with Common Table Expressions including recursive Common Table Expressions to break multi step analytical pipelines and to handle hierarchical or iterative problems, and choosing between window functions, GROUP BY, joins, and subqueries for correctness and readability. Performance and correctness considerations are essential, including join and sort costs, index usage, memory and sort spill behavior, execution planning and query optimization techniques, and trade offs across different database dialects and large data volumes. Interview assessments typically ask candidates to write and explain queries that use these functions, reason about frame semantics for edge cases such as ties, nulls, and partition boundaries, and to rewrite or optimize expensive queries.

MediumTechnical

65 practiced

Given purchases(user_id, purchase_date, purchase_amount), write a query to return each user's 3rd purchase date using NTH_VALUE and also provide an alternative implementation using ROW_NUMBER(). Explain trade-offs and portability between the two approaches across different SQL dialects.

Sample Answer

To get each user’s 3rd purchase date using NTH_VALUE (window function) and an alternative with ROW_NUMBER():

Using NTH_VALUE (returns value repeated for every row in the partition; need filtering to one row per user):

sql

SELECT DISTINCT
  user_id,
  NTH_VALUE(purchase_date, 3) OVER (PARTITION BY user_id ORDER BY purchase_date
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_purchase_date
FROM purchases;

Using ROW_NUMBER (explicitly finds the 3rd row per user):

sql

SELECT user_id, purchase_date AS third_purchase_date
FROM (
  SELECT
    user_id,
    purchase_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date) AS rn
  FROM purchases
) t
WHERE rn = 3;

Trade-offs and portability:- ROW_NUMBER is widely supported (Postgres, MySQL 8+, SQL Server, Oracle, BigQuery) and straightforward: it directly identifies the nth row and is easy to filter. It’s usually preferred for "nth row" lookups and has predictable behavior with ORDER BY ties if you add tie-breakers.- NTH_VALUE is part of ANSI SQL and supported in Oracle, Postgres, SQL Server (2012+), BigQuery—but not in some older engines. NTH_VALUE returns the value for the nth row but is a window expression, often repeating across rows; you commonly need DISTINCT or another dedup step. Its frame specification matters: to access the nth element in the whole partition reliably, use a full-partition frame (UNBOUNDED PRECEDING ... UNBOUNDED FOLLOWING).- Performance: both perform windowing; ROW_NUMBER with filtering can be faster because engine can short-circuit in some implementations. For large datasets, ensure proper indexing and consider limiting scanned rows (e.g., pre-filter by date ranges).Edge cases:- Users with fewer than 3 purchases return NULL (NTH_VALUE) or are excluded (ROW_NUMBER WHERE rn=3). Decide which behavior you want and adjust (LEFT JOIN of ROW_NUMBER subquery to user list to show NULLs).

MediumTechnical

70 practiced

Write a query to find the top 3 orders by amount for each user given orders(order_id, user_id, amount, order_date). Explain two different implementations (ROW_NUMBER() filtering in an outer query and a lateral/subquery approach) and discuss trade-offs in terms of readability and performance for a data warehouse with millions of users.

Sample Answer

Approach 1 — ROW_NUMBER() with outer filterUse a window function to rank orders per user, then filter for top 3.

sql

SELECT order_id, user_id, amount, order_date
FROM (
  SELECT
    order_id,
    user_id,
    amount,
    order_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC, order_date DESC) AS rn
  FROM orders
) t
WHERE rn <= 3;

Why: single pass that assigns ranks per user; expressive and easy to read. Works well in most warehouses (BigQuery, Redshift, Snowflake, Postgres).

Approach 2 — LATERAL (or correlated subquery) returning top 3 per userScan users and fetch top 3 orders per user via lateral join.

sql

SELECT u.user_id, o.order_id, o.amount, o.order_date
FROM (SELECT DISTINCT user_id FROM orders) u
CROSS JOIN LATERAL (
  SELECT order_id, amount, order_date
  FROM orders o
  WHERE o.user_id = u.user_id
  ORDER BY amount DESC, order_date DESC
  LIMIT 3
) o;

Why: explicit per-user top-N retrieval; can be more efficient if the engine can push down LIMIT and there are indexes/partitions on user_id.

Trade-offs (readability & performance)- Readability: ROW_NUMBER is concise and familiar for analysts. LATERAL is also readable but slightly more advanced.- Performance: ROW_NUMBER often performs a full repartition and sorts all rows by user — expensive when many users and many orders. LATERAL can avoid global sorting by doing a small top-K per user; it benefits when orders are clustered/partitioned by user and the engine supports efficient indexed access. However, LATERAL can be slower if there are millions of users and no good index, because it may execute many small subqueries.- Scalability: For millions of users, prefer solutions leveraging table partitioning, indexes, or engine-specific optimizations (TOP N per partition operators). Test both with EXPLAIN and on representative data; consider materialized summaries (pre-aggregated top-3) for frequent queries.

HardTechnical

68 practiced

When deduplicating records by selecting the 'best' row per group, explain how tie-handling can affect correctness. Given a dataset where two rows have identical ranking keys and timestamps, propose deterministic tie-breaker strategies in SQL and show an example using ROW_NUMBER with multiple ORDER BY keys to ensure idempotent results.

Sample Answer

Tie-handling matters because when you choose "best" row per group and two (or more) rows tie on your ranking keys, a nondeterministic tie-break can return different survivors across runs (different physical order, different query planner choices). That breaks idempotence, causes flapping, and can invalidate downstream analytics or incremental pipelines.

Deterministic tie-breaker strategies (choose one or combine them):- Prefer a stable unique column (primary key, surrogate id) as final tie-breaker.- Use ingestion metadata (created_at, source_system, batch_id) with well-defined precedence.- Compute a deterministic hash of all columns (or subset) and order by it — portable and stable.- Combine multiple keys in ORDER BY to make a total ordering.

Example: use ROW_NUMBER with multiple ORDER BY keys so the ORDER is total and repeatable. Here we prefer highest score, then latest ts, then deterministic hash, then id as last resort.

sql

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY score DESC,
               event_ts DESC,
               md5(concat_ws('||', coalesce(col1,''), coalesce(col2,''), coalesce(col3,''))) ASC,
               id ASC
    ) AS rn
  FROM events
)
SELECT * 
FROM ranked
WHERE rn = 1;

Why this works:- ORDER BY includes enough keys to create a total order across all rows in a partition.- md5(concat_ws(...)) produces a deterministic tie-break based on content.- Including id (primary key) ensures uniqueness if hashes collide.- Result is idempotent: same input -> same winner.

Edge notes:- Ensure stable functions (avoid random() or non-deterministic timestamps).- When using hashes, include all columns affecting downstream semantics.- Document tie rules for auditability and reproducibility.

HardTechnical

61 practiced

Compare how Postgres, Snowflake, and BigQuery differ in: default frame semantics for ORDER BY in windows, support for RANGE on timestamps, support for ordered-set aggregates as window functions, and parallelism/partitioning behavior. For a migration of a Postgres analytic query heavy on LAST_VALUE and RANGE frames to BigQuery, list concrete changes you'd expect to make.

Sample Answer

High-level comparison (Postgres vs Snowflake vs BigQuery)

- Default frame semantics for ORDER BY in windows - Postgres: ORDER BY in window defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for value-ordered (RANGE) unless you specify frame; LAST_VALUE semantics can be surprising because CURRENT ROW includes peers. - Snowflake: Default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for ORDER BY with implicit peers handling similar to Postgres; also supports ROWS default when ORDER BY absent in some functions. - BigQuery: Default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for ORDER BY on numeric/timestamp; however BigQuery historically treats ORDER BY with window functions and LAST_VALUE differently — it often requires explicit frame to get deterministic behavior (BigQuery’s LAST_VALUE defaults can include following rows unless bounded).

- Support for RANGE on timestamps - Postgres: Full support for RANGE with interval expressions on timestamp/orderable types (e.g., RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW). - Snowflake: Supports RANGE with INTERVAL on timestamps, but with some limitations — better to test peer behavior. - BigQuery: Does NOT support RANGE frames over non-numeric ORDER BY directly; RANGE with INTERVAL on TIMESTAMP is limited/unsupported. You must convert timestamps to numeric (UNIX_SECONDS/UNIX_MILLIS) or use ROWS-based frames or use analytic expressions with explicit joins.

- Support for ordered-set aggregates as window functions - Postgres: Supports ordered-set and hypothetical-set aggregates (e.g., percentile_disc, percentile_cont) but they are not usable as window functions in all contexts; some ordered-set functions support OVER(). - Snowflake: Supports many ordered-set aggregates (e.g., APPROX_PERCENTILE) and allows them as window functions with OVER(). - BigQuery: Has analytic percentile_* window functions (e.g., APPROX_QUANTILES) but traditional ordered-set aggregates from Postgres are not directly available; syntax and semantics differ.

- Parallelism / partitioning behavior - Postgres: Parallelism depends on planner/worker processes; window functions with PARTITION BY may inhibit some parallelization; large-memory sorts can be a bottleneck unless query is tuned (indexes, partitioned tables). - Snowflake: Massively parallel, automatically scales; window operations are distributed with clustering but can be influenced by clustering keys. - BigQuery: Fully distributed, columnar, high parallelism. However, window functions that require large sorts/shuffles (wide partition, high-cardinality ORDER BY) can be expensive; using PARTITION BY reduces shuffle and improves performance.

Concrete changes when migrating Postgres analytics heavy on LAST_VALUE and RANGE frames to BigQuery

1. Explicitly define frames: Replace reliance on implicit RANGE semantics by adding explicit frames. For LAST_VALUE to get “last up to current” behavior, use: - LAST_VALUE(x) OVER (PARTITION BY ... ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - Or use ARRAY_AGG with LIMIT/ORDER BY in BigQuery: (ARRAY_AGG(x ORDER BY ts DESC LIMIT 1) OVER (PARTITION BY ...)) to avoid frame ambiguity.

2. Handle timestamp RANGE frames: Convert timestamp ORDER BY to numeric epoch if you need RANGE-based offset behavior: - Use UNIX_MILLIS(ts) and then use numeric RANGE, or emulate time-based window by self-joining with BETWEEN ts - INTERVAL and ts. - Prefer ROWS-based frames or ARRAY_AGG-based sliding windows for precise control.

3. Emulate ordered-set aggregates: Replace Postgres ordered-set aggregates with BigQuery equivalents: - Use APPROX_QUANTILES or PERCENTILE_CONT/SAFE functions available in BigQuery, or ARRAY_AGG + OFFSET to get exact percentiles.

4. Rework performance-sensitive partitioning: - Add PARTITION BY clauses to window functions to limit shuffles. - Push predicates early, use clustered tables or partitioned tables (date partitioning) in BigQuery to reduce scanned data. - Convert correlated window-heavy queries into analytic-friendly forms: pre-aggregate in subqueries, use QUALIFY to filter window results, or use windowed ARRAY_AGG to compute per-partition results then join.

5. Test edge cases: - Verify peer (tie) behavior for equal ORDER BY values. - Confirm NULL handling differences for LAST_VALUE and other aggregates. - Validate results on boundary timestamps when emulating RANGE.

Small example replacement pattern (conceptual):- Postgres: LAST_VALUE(val) OVER (PARTITION BY id ORDER BY ts RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW)- BigQuery: (SELECT val FROM UNNEST(ARRAY_AGG(val ORDER BY ts DESC)) WITH OFFSET AS off WHERE ts BETWEEN TIMESTAMP_SUB(current_ts, INTERVAL 1 DAY) AND current_ts LIMIT 1) OVER (PARTITION BY id) — or simpler: use ARRAY_AGG(val ORDER BY ts DESC LIMIT 1) OVER (PARTITION BY id) with pre-filtered rows.

Expect to add explicit framing, convert time RANGE semantics into numeric or join/ARRAY approaches, replace unsupported aggregates, and tune partitions/cluster keys to restore performance.

MediumTechnical

66 practiced

A query using ORDER BY inside multiple window functions is performing poorly and the explain plan shows several large sorts and some 'sort spill to disk' operations. As a data analyst responsible for performance, list and explain at least five concrete optimization steps you would take to reduce sort/spill costs and speed up the query.

Unlock Full Question Bank

Get access to hundreds of Advanced SQL Window Functions interview questions and detailed answers.

Join thousands of developers preparing for their dream job.