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.

EasyTechnical

61 practiced

Explain the difference between ROWS and RANGE frame specifications in the OVER clause. Provide an example dataset where ROWS BETWEEN 1 PRECEDING AND CURRENT ROW gives different results from RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW due to duplicate timestamps or value ties.

Sample Answer

ROWS frames are positional: "ROWS BETWEEN 1 PRECEDING AND CURRENT ROW" returns the current row plus exactly one physical prior row in the sorted order. RANGE frames are value-based: "RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW" returns all rows whose ordering value (timestamp or numeric) falls within the value range relative to the current row — that can include multiple tied rows with the same timestamp.

Example dataset (ordered by ts):

id | ts1 | 2025-01-01 10:00:002 | 2025-01-01 10:00:003 | 2025-01-01 09:59:004 | 2025-01-01 09:58:00

Calculate cumulative count using ROWS vs RANGE:

sql

-- using ROWS: includes current row + one previous physical row
SELECT id, ts,
  COUNT(*) OVER (ORDER BY ts ASC, id ASC
                 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS cnt_rows
FROM t
ORDER BY ts, id;

-- using RANGE 1 day: includes all rows whose ts >= ts - 1 day
SELECT id, ts,
  COUNT(*) OVER (ORDER BY ts ASC
                 RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW) AS cnt_range
FROM t
ORDER BY ts, id;

Result explanation:- For id=1 (10:00) ROWS count = 2 (includes id=3 or id=2 depending on tie-breaker by id), but for id=2 (also 10:00) ROWS count = 2 (current + the single previous physical). RANGE count for both id=1 and id=2 = 3 because RANGE includes both tied 10:00 rows and the 09:59 row (all within 1 day). Thus RANGE can include multiple tied rows; ROWS strictly uses physical row positions.

MediumTechnical

77 practiced

Compute a 7-day moving average of daily active users per product using window functions. Table: daily_activity(product_id, day date, active_users int). Provide SQL that handles missing days (treat missing day as zero) and explain how you accommodate gaps so the 7-day window is accurate.

Sample Answer

Approach: build a complete per-product date series for the range you care about, left-join daily_activity (treat NULL as 0), then compute a 7-day moving average with window functions partitioned by product and ordered by day. This guarantees missing days count as zero in the 7-day window.

sql

-- PostgreSQL example
WITH bounds AS (
  -- overall date range; adjust as needed
  SELECT MIN(day) AS min_day, MAX(day) AS max_day FROM daily_activity
),
products AS (
  SELECT DISTINCT product_id FROM daily_activity
),
calendar AS (
  -- generate full date series across products
  SELECT p.product_id, g::date AS day
  FROM products p
  CROSS JOIN LATERAL (
    SELECT generate_series(b.min_day, b.max_day, interval '1 day') AS g
    FROM bounds b
  ) s
),
filled AS (
  -- left join actual activity, treat missing as zero
  SELECT
    c.product_id,
    c.day,
    COALESCE(a.active_users, 0) AS active_users
  FROM calendar c
  LEFT JOIN daily_activity a
    ON a.product_id = c.product_id AND a.day = c.day
)
SELECT
  product_id,
  day,
  active_users,
  -- 7-day moving average including current day and previous 6 days
  ROUND(
    AVG(active_users) OVER (
      PARTITION BY product_id
      ORDER BY day
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )::numeric, 2
  ) AS moving_avg_7d
FROM filled
ORDER BY product_id, day;

Why this works:- generate_series creates explicit rows for every date per product so gaps become rows with active_users = 0.- The window frame ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ensures a fixed 7-row window (exactly 7 days) even if original data missed days.- Partitioning by product keeps averages per product.

Notes & alternatives:- If you need 7 calendar days regardless of missing timestamps, this is correct. If you want 7 most recent observed days (skip zeros), use RANGE with FILTER or use ROWS on ranked observed-days.- For large product/date cardinality, restrict bounds per product to reduce work (generate per-product min/max) or pre-aggregate, and ensure appropriate indexes.

EasyTechnical

60 practiced

Explain difference between percent_rank() and cume_dist(). Provide a small example dataset and show the numeric outputs for both functions. Describe which is more appropriate for percentile-based dashboard buckets and why.

EasyTechnical

74 practiced

Compare ROW_NUMBER, RANK, DENSE_RANK, and NTILE. For each function explain tie-handling, example use cases in BI dashboards (e.g., top-N, percentile buckets), and when one would be preferred over another. Show an example where RANK and DENSE_RANK produce different results.

Sample Answer

ROW_NUMBER, RANK, DENSE_RANK, and NTILE are window functions used to assign positions to rows — important in BI for Top-N, ties, and percentile buckets.

- ROW_NUMBER() - Tie-handling: assigns unique sequential numbers arbitrarily within ties (no equal numbers). - BI uses: strict Top-N (e.g., show exactly 10 rows even if ties exist); pagination. - Prefer when you need deterministic row positions (use with ORDER BY plus tiebreaker).

- RANK() - Tie-handling: equal rows share the same rank; gaps appear after ties (1,1,3). - BI uses: Top-N with ties honored (show everyone tied for rank <= N); leaderboards where gaps reflect tie counts. - Prefer when rank gaps matter.

- DENSE_RANK() - Tie-handling: equal rows share same rank; no gaps after ties (1,1,2). - BI uses: compact ranking for grouping top buckets (e.g., distinct rank labels on chart). - Prefer when you want contiguous ranks for categories.

- NTILE(n) - Tie-handling: divides rows into n buckets as evenly as possible; assignment within ties depends on ORDER BY and implementation (can split ties across buckets). - BI uses: percentile/quartile buckets (e.g., top 10%/quartiles) for segmenting customers. - Prefer for percentile segmentation; be careful with many equal values.

Example showing RANK vs DENSE_RANK difference:

sql

WITH sales AS (
  SELECT 'A' AS product, 100 AS amt UNION ALL
  SELECT 'B', 100 UNION ALL
  SELECT 'C', 90 UNION ALL
  SELECT 'D', 80
)
SELECT product, amt,
  RANK() OVER (ORDER BY amt DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY amt DESC) AS drnk
FROM sales;

Result:- A: amt=100, rnk=1, drnk=1- B: amt=100, rnk=1, drnk=1- C: amt=90, rnk=3, drnk=2 <-- shows the gap difference- D: amt=80, rnk=4, drnk=3

Tips: choose ROW_NUMBER for exact row limits, RANK to reflect competition with gaps, DENSE_RANK for compact categories, NTILE for percentile buckets (verify tie behavior).

HardTechnical

62 practiced

You're asked to compute percent_rank for millions of users and then join the results back to the user table to label elite users. Discuss performance implications of computing percent_rank across a huge dataset and propose alternatives (approximate quantiles, ntile, precomputed histograms). Provide SQL or architecture sketches for each alternative.

Sample Answer

Requirements & concern: percent_rank requires sorting all users by metric and computing window percentiles; for millions of users this is expensive (full shuffle, large sort, memory pressure) and slow for frequent/near-real-time labeling. Alternatives trade accuracy for speed.

1) Exact percent_rank (baseline) — expensive- Heavy GROUP BY + window sort; requires cluster-wide shuffle.- SQL sketch:

sql

SELECT user_id,
       percent_rank() OVER (ORDER BY metric) AS pct
FROM users;

Use only if runs infrequently and cluster can afford sort; otherwise avoid.

2) Approximate quantiles (recommended for scale)- Use DB built-ins (BigQuery/Presto/Redshift Spectrum) or t-digest in Spark.- BigQuery example:

sql

SELECT user_id,
       APPROX_QUANTILES(metric, 100)[OFFSET( ROUND(APPROX_QUANTILE_POSITION*100) )] AS pct_bucket
FROM users;

- Spark sketch (t-digest): aggregate t-digests per partition, merge, then map users to quantile buckets with broadcasted digest. Low memory, single shuffle on digests.

3) NTILE buckets (fast, less precise)- NTILE(100) avoids computing continuous percent_rank; can be done in distributed systems with partition-aware sorts, cheaper.

sql

SELECT user_id, NTILE(100) OVER (ORDER BY metric DESC) AS decile
FROM users;

- Use when labeling (top 1%) — compute top N via window with partitioning or using WHERE metric >= threshold from histogram.

4) Precomputed histograms / materialized aggregates (best for frequent queries)- Periodically compute histogram/quantile thresholds (hourly/daily) and store thresholds table.- Architecture: - Batch job computes thresholds (approx_percentile or t-digest) -> thresholds table. - Join users to thresholds to label elites:

sql

SELECT u.user_id, u.metric, t.threshold
FROM users u
JOIN thresholds t ON u.metric >= t.p99_value

- Very fast joins, minimal recompute. For streaming, update digest incrementally.

Trade-offs & notes:- Approx methods: small error but huge speed gains.- NTILE simple but coarse; use for UI buckets.- Precompute + join: best for operational labeling and reproducibility.- For top-1% detection, compute threshold once (approx) then use index scan to select elites — avoids full sort.

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.