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.

HardTechnical

67 practiced

Advanced gap-and-island: In table events(user_id int, event_time timestamp, status text) identify islands where status = 'active' for contiguous periods and compute total active time per island, allowing a tolerance of 5 minutes between events to still count as contiguous. Provide a scalable SQL solution suitable for billions of rows.

Sample Answer

Approach: sort events per user, detect breaks where gap > 5 minutes, assign an island id by cumulatively counting breaks, then aggregate start/end and total active time per island. For scale use partitioned/clustered tables and a distributed engine (Spark, BigQuery, Snowflake). Example uses ANSI SQL with seconds diff; adjust functions for your platform.

sql

-- BigQuery / Postgres-ish syntax (use TIMESTAMP_DIFF/EXTRACT as appropriate)
WITH ordered AS (
  SELECT
    user_id,
    event_time,
    status,
    LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time
  FROM events
  WHERE status = 'active'  -- filter early to reduce data
),
marks AS (
  SELECT
    user_id,
    event_time,
    CASE
      WHEN prev_time IS NULL THEN 1
      WHEN EXTRACT(EPOCH FROM (event_time - prev_time)) > 300 THEN 1  -- gap > 5 min
      ELSE 0
    END AS new_island_flag
  FROM ordered
),
islands AS (
  SELECT
    user_id,
    event_time,
    SUM(new_island_flag) OVER (PARTITION BY user_id ORDER BY event_time ROWS UNBOUNDED PRECEDING) AS island_seq
  FROM marks
)
SELECT
  user_id,
  island_seq AS island_id,
  MIN(event_time) AS island_start,
  MAX(event_time) AS island_end,
  -- If events are instantaneous, total active time = island_end - island_start.
  -- If events represent heartbeats, you may add assumed session granularity.
  EXTRACT(EPOCH FROM (MAX(event_time) - MIN(event_time))) AS total_active_seconds,
  COUNT(*) AS event_count
FROM islands
GROUP BY user_id, island_seq;

Key points & scalability:- Filter status='active' early to reduce data scanned.- Ensure events are clustered/partitioned by user_id and time (e.g., Hive partition by date, cluster by user_id) so windowing is local per partition.- On Spark, use DataFrame APIs with repartition(user_id) then sortWithinPartitions to avoid cross-partition shuffling.- For billions of rows, store as compressed columnar (Parquet/ORC), use predicate pushdown on date ranges, and maintain appropriate table clustering/indexes.Edge cases:- Duplicate timestamps — dedupe or define ordering.- Sparse heartbeats — if events are instantaneous, consider adding a fixed session extension (e.g., +1 minute) before computing durations.- Very long islands — cap or sample if needed for downstream limits.

MediumTechnical

60 practiced

Compare window frame and function differences across at least three SQL dialects (Postgres, BigQuery, Snowflake, Redshift). Focus on support for RANGE with INTERVAL, IGNORE NULLS, default frame behavior for FIRST_VALUE/LAST_VALUE, and limits on window frame expressions. What portability pitfalls should a data engineer be aware of?

Sample Answer

Situation: As a data engineer, you’ll often port analytic SQL between Postgres, BigQuery, Snowflake, and Redshift; window-frame semantics differ and break queries subtly. Below is a focused comparison and portability pitfalls.

RANGE with INTERVAL- Postgres: Supports RANGE BETWEEN x PRECEDING/FOLLOWING with INTERVAL for timestamp/date types (e.g., RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW).- Snowflake: Supports RANGE with INTERVAL for numeric and timestamp (but behavior can be implementation-specific for fractional units).- BigQuery: Supports RANGE on numeric/temporal via "RANGE BETWEEN INTERVAL …" only in SQL standard functions? Historically BigQuery only supported ROWS and requires explicit CASTs; recent versions added limited RANGE support—test on your version.- Redshift: Limited—does not support RANGE with INTERVAL for timestamps; mostly ROWS-based.

IGNORE NULLS (for FIRST_VALUE/LAST_VALUE)- Postgres: Has IGNORE NULLS (since v11+ for some functions via FILTER?); generally lacks uniform support—use conditional expressions (e.g., FIRST_VALUE(x) OVER (...) FILTER (WHERE x IS NOT NULL) is not equivalent).- Snowflake: Supports IGNORE NULLS directly.- BigQuery: Supports IGNORE NULLS for FIRST_VALUE/LAST_VALUE.- Redshift: Does not support IGNORE NULLS; need workarounds (e.g., use QUALIFY with ROW_NUMBER over partition of non-null values or use custom aggregates).

Default frame behavior for FIRST_VALUE/LAST_VALUE- Postgres/Snowflake/BigQuery: FIRST_VALUE/LAST_VALUE default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — so LAST_VALUE can be surprising (it returns last within frame, often current row); to get partition-level last value you must use frame RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING or use MAX() OVER().- Redshift: Tends to default similarly but because RANGE is limited, behavior may differ; safest to specify explicit frame.

Limits on window frame expressions- Some engines (BigQuery, Snowflake) allow expressions like INTERVAL literals; Postgres allows complex expressions. Redshift restricts non-constant expressions and disallows certain INTERVAL usages. Also many engines limit RANGE to ORDER BY on a single numeric/timestamp column.

Portability pitfalls & best practices- Don’t rely on implicit RANGE support for temporal intervals—use explicit ROWS or compute bounds with correlated values (lead/lag) or join to derived bounds.- IGNORE NULLS portability: don’t assume availability—normalize via filtering or use ROW_NUMBER over non-null ordering to pick first/last non-null.- Always specify the frame explicitly when you mean partition-level FIRST/LAST (use UNBOUNDED FOLLOWING for LAST_VALUE).- Test on target engine versions—features are added between releases.- Prefer ANSI-standard constructs where supported; add engine-specific guarded SQL or transformation steps in ETL to normalize behavior.- Document and add unit tests for windowed logic to catch subtle runtime differences.

HardTechnical

78 practiced

Events are stored as a JSON array in a single column per user: user_events(user_id int, events jsonb). Each events array contains objects with event_time and type. Write SQL (Postgres or Snowflake) to return the timestamp of the 2nd event per user. Provide an approach using NTH_VALUE or unnest-with-ordinality and explain trade-offs.

Sample Answer

Approach summary:- Two options: (A) use a window NTH_VALUE over exploded rows (no explicit ordinals required), (B) unnest/flatten with ordinality and pick ordinality = 2. Both ensure correct ordering by event_time — important because JSON array order may not equal chronological order.

Postgres — NTH_VALUE (exploded with LATERAL + window):

sql

SELECT DISTINCT user_id,
       NTH_VALUE(ev->>'event_time', 2) OVER (PARTITION BY user_id ORDER BY (ev->>'event_time')::timestamptz
                                             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_event_time
FROM user_events,
LATERAL jsonb_array_elements(events) AS t(ev);

Postgres — unnest with ordinality (explicit 2nd element by position AFTER ordering):(If you want the 2nd by array position)

sql

SELECT ue.user_id,
       elt->>'event_time' AS second_event_time
FROM user_events ue,
LATERAL (
  SELECT e AS elt
  FROM jsonb_array_elements(ue.events) WITH ORDINALITY arr(e, ord)
  WHERE ord = 2
) s;

Snowflake — NTH_VALUE over FLATTEN:

sql

SELECT DISTINCT user_id,
       NTH_VALUE(value: event_time::string, 2) OVER (PARTITION BY user_id
                                                    ORDER BY TO_TIMESTAMP(value:event_time::string)
                                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_event_time
FROM user_events,
LATERAL FLATTEN(input => events);

Snowflake — FLATTEN with index (sequence starts at 0):

sql

SELECT user_id,
       value:event_time::string AS second_event_time
FROM user_events,
LATERAL FLATTEN(input => events) f
WHERE f.seq = 1; -- seq=1 -> 2nd element (0-based)

Key trade-offs and notes:- NTH_VALUE approach orders by event_time so it returns the 2nd chronological event regardless of array position. Good if events may be out of order. It can be heavier: windowing across all events per user (memory/cost on large partitions).- Ordinality/seq approach is cheaper and simpler if you need the 2nd element by array position (O(n) scan but no window), but it fails when array order isn't chronological.- Parsing/casting: cast event_time to timestamptz/TO_TIMESTAMP for correct ordering and timezone handling.- Nulls & fewer-than-2 events: NTH_VALUE returns NULL; ordinality query yields no row — decide whether to emit NULLs (LEFT JOIN) or filter.- Performance: for very large JSON arrays or high-cardinality users, exploding + window can be expensive — consider pre-normalizing JSON into a table (ETL) for repeated queries.

MediumTechnical

78 practiced

Write a SQL query to return top 5 products by revenue per category but if the 5th rank is tied among multiple products include all products that tie for 5th place. Table: sales(product_id, category, revenue). Explain your choice of ranking function.

Sample Answer

Approach: Use a window ranking function partitioned by category ordered by revenue descending. To include ties at the 5th position, use the RANK() function (which gives equal rank for tied revenues and can make multiple rows share rank 5). Then filter ranks <= 5.

sql

SELECT product_id, category, revenue, rnk
FROM (
  SELECT
    product_id,
    category,
    revenue,
    RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
  FROM sales
) t
WHERE rnk <= 5
ORDER BY category, rnk, revenue DESC;

Why RANK(): RANK() assigns the same rank to identical revenue values and will skip ranks after ties (e.g., 1,1,3). This ensures that if several products tie at the 5th rank they all receive rnk = 5 and are included. ROW_NUMBER() would arbitrarily break ties (exclude some), and DENSE_RANK() would not skip ranks but still include ties — it also works here, but DENSE_RANK() could produce more than 5 distinct ranks when many ties above shift the 5th distinct revenue; RANK() aligns with the requirement "top 5 by rank including ties at 5th". Edge cases: categories with fewer than 5 products return all; null revenues should be handled explicitly if needed (e.g., ORDER BY revenue DESC NULLS LAST).

EasyTechnical

75 practiced

Given table customers(customer_id int, email text, name text, updated_at timestamp), write a SQL statement to deduplicate customers by email keeping only the row with the latest updated_at per email. Provide a safe (idempotent) approach for Postgres that can be run in production and explain trade-offs.

Sample Answer

Approach: use row_number() to pick the latest row per email, keep those, and delete others in a safe transaction. To be idempotent run it repeatedly — once duplicates are gone it does nothing. Prefer batching on large tables and wrap in a transaction; add a unique constraint afterward to prevent recurrence.

Example (single-statement safe delete using CTE):

sql

WITH to_keep AS (
  SELECT MIN(customer_id) AS customer_id  -- pick deterministic id when timestamps tie
  FROM (
    SELECT customer_id, email,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC, customer_id ASC) AS rn
    FROM customers
  ) t
  WHERE rn = 1
)
DELETE FROM customers c
USING customers x
WHERE c.email = x.email
  AND c.customer_id <> x.customer_id
  AND x.customer_id IN (SELECT customer_id FROM to_keep)
;

Safer variant (recommended for production):1. Create a deduplication table of rows to keep.

sql

BEGIN;
CREATE TEMP TABLE keep_ids AS
SELECT customer_id
FROM (
  SELECT customer_id,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC, customer_id ASC) rn
  FROM customers
) t
WHERE rn = 1;
DELETE FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM keep_ids);
-- Optionally: add UNIQUE(email) CONSTRAINT after verifying
COMMIT;

Why this is safe/idempotent:- Deterministic tie-breaker (customer_id) avoids nondeterminism.- Wrapped in transaction ensures atomicity.- Running again does nothing (no rows to delete) — idempotent.- Creating a temp table isolates selection from concurrent changes.

Trade-offs and considerations:- Performance: window functions scan the table; for very large tables do batched deletes (LIMIT + ORDER BY) to avoid long locks and vacuum bloat.- Locks: DELETE acquires row locks; avoid full-table locks. Run during low-traffic windows or use smaller transactions.- Foreign keys: deleting rows referenced elsewhere will fail — either cascade or update FKs to the kept id first.- Uniqueness: after cleanup, add UNIQUE(email) to prevent recurrence.- Ties: choice of tie-breaker (customer_id) must match business rules; you might prefer latest non-null fields or more complex merge logic.

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.