Advanced SQL: Window Functions & CTEs for Complex Analysis Questions

Advanced SQL techniques using window functions (ROW_NUMBER, RANK, DENSE_RANK, etc.) and common table expressions (CTEs), including recursive queries, for complex data analysis, ranking and analytics patterns, cumulative totals, and multi-step data transformations within relational databases and data warehousing contexts.

HardTechnical

53 practiced

Write SQL that computes percent change vs prior period and flags statistically significant changes using a z-score approximation. Table: daily_sales(store_id, sale_date DATE, revenue NUMERIC). Return store_id, sale_date, revenue, pct_change, z_score_flag. Describe assumptions.

Sample Answer

Approach: compute day-over-day percent change per store, then compute a rolling baseline mean and stddev of prior pct_changes (e.g., 30-day window) and flag days whose pct_change deviates by more than 1.96 std (approx. 95% two-sided z-test). Handle prior revenue = 0 and require at least a minimum sample size.

sql

WITH base AS (
  SELECT
    store_id,
    sale_date,
    revenue,
    LAG(revenue) OVER (PARTITION BY store_id ORDER BY sale_date) AS prev_revenue
  FROM daily_sales
),
pct AS (
  SELECT
    store_id,
    sale_date,
    revenue,
    CASE
      WHEN prev_revenue IS NULL THEN NULL
      WHEN prev_revenue = 0 THEN NULL        -- avoid divide-by-zero; treat as NULL or handle separately
      ELSE (revenue - prev_revenue)::NUMERIC / prev_revenue
    END AS pct_change
  FROM base
),
rolling AS (
  SELECT
    store_id,
    sale_date,
    revenue,
    pct_change,
    -- rolling mean and stddev of pct_change over the prior 30 days (excluding current day)
    AVG(pct_change)  OVER (PARTITION BY store_id ORDER BY sale_date
                           ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING) AS mean_pct,
    STDDEV_SAMP(pct_change) OVER (PARTITION BY store_id ORDER BY sale_date
                                  ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING) AS std_pct,
    COUNT(pct_change) OVER (PARTITION BY store_id ORDER BY sale_date
                            ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING) AS n_prior
  FROM pct
)
SELECT
  store_id,
  sale_date,
  revenue,
  pct_change,
  CASE
    WHEN pct_change IS NULL THEN FALSE
    WHEN std_pct IS NULL OR std_pct = 0 THEN FALSE      -- insufficient variation
    WHEN n_prior < 10 THEN FALSE                       -- require >=10 prior samples (tunable)
    WHEN ABS( (pct_change - mean_pct) / std_pct ) > 1.96 THEN TRUE
    ELSE FALSE
  END AS z_score_flag
FROM rolling
ORDER BY store_id, sale_date;

Assumptions & notes:- Baseline window = prior 30 days; sample size threshold = 10; z cutoff = 1.96 (95%).- STDDEV_SAMP gives sample stddev; if std = 0 or insufficient n, we don’t flag.- prev_revenue = 0 is treated as NULL (could alternatively flag separately or use small epsilon).- This is an approximation: percent-change distribution may be non-normal; for small n or heavy tails consider non-parametric tests or log-transformations, or use robust methods (MAD) or control charts for production.

EasyTechnical

52 practiced

Explain the differences between ROW_NUMBER(), RANK(), and DENSE_RANK() window functions. For each function, give a short example dataset and describe the result. As a BI analyst, when would you choose one over the others for leaderboard and tie-handling scenarios?

Sample Answer

ROW_NUMBER(), RANK(), and DENSE_RANK() all assign ordinal numbers over a window ordered by some expression, but they handle ties differently.

ROW_NUMBER(): assigns a unique sequential integer to each row (no ties — arbitrary tie break by row order).Example dataset:sales: (person, sales) = (A,100),(B,100),(C,90)SQL:

sql

SELECT person, sales,
 ROW_NUMBER() OVER (ORDER BY sales DESC) AS rn
FROM sales;

Result rn: A=1, B=2, C=3

RANK(): gives the same rank to tied values, but skips following ranks (creates gaps).Same data:

sql

SELECT person, sales,
 RANK() OVER (ORDER BY sales DESC) AS r
FROM sales;

Result r: A=1, B=1, C=3 -- rank 2 skipped

DENSE_RANK(): same as RANK() for ties but does not skip ranks — dense sequence.

sql

SELECT person, sales,
 DENSE_RANK() OVER (ORDER BY sales DESC) AS dr
FROM sales;

Result dr: A=1, B=1, C=2

When to use which (BI / leaderboard context):- Use ROW_NUMBER() when you need a deterministic, unique ordering (e.g., pick a single “top” row per group for de-duplication).- Use RANK() when you want traditional competition-style ranks where ties share a rank and subsequent positions reflect competitor counts (useful for prize allocation where skipped positions matter).- Use DENSE_RANK() when you want tied items to share a rank but want contiguous rank numbers (good for displaying position labels on dashboards without gaps).

Also consider showing both rank and tie count or using ties indicator in visuals to avoid ambiguity.

MediumTechnical

57 practiced

Write SQL that computes a running total on a very large dataset but restricts the frame to the last 90 days for each user to limit work per row. Table: events(user_id, event_time TIMESTAMP, value NUMERIC). Return user_id, event_time, value, running_90d_total.

Sample Answer

Approach: use a RANGE or ROWS sliding window can't easily express "last 90 days" across timestamps in many engines, so use a correlated windowed SUM with window frame based on event_time using RANGE BETWEEN INTERVAL or an efficient self-join/aggregate with lateral to bound work per row. Below are two performant patterns (pick based on DB: Postgres, Redshift, BigQuery, Snowflake).

Postgres / Snowflake (window with RANGE on intervals supported):

sql

SELECT
  user_id,
  event_time,
  value,
  SUM(value) OVER (
    PARTITION BY user_id
    ORDER BY event_time
    RANGE BETWEEN INTERVAL '90 days' PRECEDING AND CURRENT ROW
  ) AS running_90d_total
FROM events

BigQuery (window RANGE with INTERVAL works on TIMESTAMP_SECONDS via UNIX_SECONDS):

sql

SELECT
  user_id,
  event_time,
  value,
  SUM(value) OVER (
    PARTITION BY user_id
    ORDER BY UNIX_SECONDS(event_time)
    RANGE BETWEEN 90 * 24 * 3600 PRECEDING AND CURRENT ROW
  ) AS running_90d_total
FROM events

Alternative (works everywhere, often more efficient for huge tables): use lateral/self-join with bounded subquery per event to limit scanned rows (ensure indexes on (user_id,event_time)):

sql

SELECT e.user_id, e.event_time, e.value,
  (SELECT SUM(value) FROM events e2
   WHERE e2.user_id = e.user_id
     AND e2.event_time BETWEEN e.event_time - INTERVAL '90 days' AND e.event_time
  ) AS running_90d_total
FROM events e;

Key points:- Ensure an index on (user_id, event_time) to limit IO.- For extremely large datasets use pre-aggregation (daily bucketed sums) and then running sum over days to reduce rows.- Validate timezone handling and inclusive/exclusive boundaries.

MediumTechnical

62 practiced

Explain how PARTITION BY affects parallelism and planner choices in databases like PostgreSQL. Provide an example where changing the partition key improves query performance for window functions on a large table.

Sample Answer

Partitioning (and PARTITION BY in window functions) influence how the planner can parallelize work and avoid expensive sorts. In PostgreSQL a window function that has PARTITION BY requires rows to be grouped by that key during evaluation; if the planner can guarantee rows for a partition are colocated (for example because the table is partitioned by the same column and a suitable index exists), it can avoid a global sort and split work across workers. If not, Postgres may perform a wide sort or disable parallel plans, increasing CPU and I/O.

Example: a transactions table with 200M rows partitioned by created_date (monthly). A dashboard query calculates running_total PARTITION BY customer_id ORDER BY created_at. Because partitioning is by date, rows for the same customer live across many partitions; Postgres must gather and sort all rows for each customer -> no effective parallelism and heavy shuffling.

Improve by partitioning on customer_id_hash (or by customer_id modulo N) so rows for a customer are colocated. Then window frames PARTITION BY customer_id can be processed per partition in parallel with only local sorts.

SQL sketch:

sql

-- before: monthly partitions
SELECT customer_id,
       created_at,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total
FROM transactions
WHERE created_at >= '2025-01-01';

-- improvement: partition by customer_shard
ALTER TABLE transactions PARTITION BY HASH (customer_id);
-- or create customer_shard column and partition by it

Result: planner can launch parallel workers that operate on distinct partitions, reduce global sorts and I/O, and speed up window computations. Trade-offs: more partitions may complicate maintenance and queries that filter by date might need extra pruning strategies; choose partitioning aligned with dominant query patterns.

HardTechnical

66 practiced

Explain SQL portability considerations when using advanced window features (e.g., RANGE with INTERVAL, implicit frame behavior) across PostgreSQL, BigQuery, Snowflake, and SQL Server. Provide examples of at least two features that behave differently and how you'd write portable SQL for BI use.

Sample Answer

Key portability risks with advanced window features arise because vendors implement the SQL standard differently (or extend it). Two common pitfalls for BI work are: (1) RANGE frames with INTERVAL (time-aware frames) and (2) implicit frame semantics / EXCLUDE (peer/duplicate handling). Below I show the differences and portable workarounds.

1) RANGE ... INTERVAL (time-aware frames)- Differences: PostgreSQL supports RANGE on ORDER BY timestamp with INTERVAL-based bounds (e.g., RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW). BigQuery historically does not support RANGE with non-numeric offsets the same way; Snowflake requires the ORDER BY expression and RANGE offset types to be compatible; SQL Server’s RANGE is limited and often behaves unexpectedly with non-deterministic ORDER BY values.- BI-safe workaround: express time windows as explicit WHERE or JOIN-based aggregations (preferred for clarity and consistent behavior) or compute frame-start per row and join/aggregate.Example (portable pattern — rolling 7-day sum using self-join aggregation):

sql

-- portable approach: compute rolling 7-day sum via correlated subquery or join
SELECT
  t.user_id,
  t.event_date,
  t.value,
  (
    SELECT SUM(x.value)
    FROM events x
    WHERE x.user_id = t.user_id
      AND x.event_date >= t.event_date - INTERVAL '6' DAY
      AND x.event_date <= t.event_date
  ) AS rolling_7d_sum
FROM events t;

This avoids engine-specific RANGE semantics and works in Postgres, Snowflake, BigQuery (use DATE_SUB for BigQuery dialect), and SQL Server (use DATEADD).

2) Implicit frame behavior & EXCLUDE / peer handling- Differences: The SQL standard default frame for a window with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; that means tied ORDER BY values (peers) are included. PostgreSQL implements EXCLUDE to remove current row or ties; BigQuery and SQL Server lack EXCLUDE; Snowflake does not support EXCLUDE either. Also, default BETWEEN behavior (ROWS vs RANGE) and how ties are handled differ.- BI-safe workaround: choose ROWS-based frames when you want deterministic row-count windows (ROWS BETWEEN N PRECEDING AND CURRENT ROW) because ROWS counts physical rows and is consistent across engines. If you need peer-aware semantics, emulate via explicit partitioning and filtering.Example (use ROWS to avoid tie/peer ambiguity):

sql

-- deterministic N-row rolling average (portable)
SELECT
  user_id,
  event_ts,
  AVG(value) OVER (
    PARTITION BY user_id
    ORDER BY event_ts
    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
  ) AS avg_last_5_rows
FROM events;

If you need to exclude the current row but EXCLUDE isn't supported, compute two windows or subtract current value:

sql

-- emulate EXCLUDE CURRENT ROW by subtracting current value
AVG(value) OVER (PARTITION BY user_id ORDER BY event_ts ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
- value  * (1.0 / 5)  -- adjust only if constant frame width; otherwise use subquery

Practical tips for BI:- Prefer explicit ROWS frames when you need deterministic row counts; prefer explicit JOIN/WHERE windows for time ranges.- Normalize dialect differences in your ETL layer (e.g., produce frame-start timestamps, then do simple joins in dashboards).- Test on sample data with tied ORDER BY values and edge dates to confirm behavior.- Keep SQL in BI tools as simple scalar aggregations or precompute complex windows in a data model/ETL where you can control dialect-specific SQL.

These strategies produce predictable results across PostgreSQL, BigQuery, Snowflake, and SQL Server and are easier to maintain for BI dashboards and reports.

Unlock Full Question Bank

Get access to hundreds of Advanced SQL: Window Functions & CTEs for Complex Analysis interview questions and detailed answers.

Join thousands of developers preparing for their dream job.