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

60 practiced

Explain the purpose and components of the SQL OVER clause when used with window functions. Describe how PARTITION BY and ORDER BY inside OVER change the result set, and provide a compact example using ROW_NUMBER() over partitions of country ordered by revenue to illustrate the differences.

Sample Answer

The OVER clause defines the window (the set of rows) a window function operates on. Components:- PARTITION BY: splits rows into groups; the function is computed independently per partition.- ORDER BY: defines ordering within each partition (or the whole set if no PARTITION); required for rank/row-number semantics and for cumulative functions.- Frame clauses (e.g., ROWS BETWEEN) further restrict the window — optional for many functions.

How PARTITION BY and ORDER BY change results:- PARTITION BY country resets numbering/aggregation per country.- ORDER BY revenue determines the sequence used for functions like ROW_NUMBER(), RANK(), or cumulative SUM.

Compact example:

sql

SELECT
  country,
  customer_id,
  revenue,
  ROW_NUMBER() OVER (PARTITION BY country ORDER BY revenue DESC) AS rn_by_country,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS rn_global
FROM sales;

Explanation:- rn_by_country = 1 for the top revenue customer within each country.- rn_global = 1 for the top revenue customer across all rows (ignores country boundaries). This shows PARTITION BY scopes the computation, ORDER BY controls ranking order.

EasyTechnical

72 practiced

Explain the difference between FIRST_VALUE and LAST_VALUE window functions, and describe a scenario where LAST_VALUE returns unexpected values due to default frame semantics. Show how to change the frame specification to get the intended 'last seen up to current row' behavior.

EasyTechnical

82 practiced

How do NULL values in columns used by PARTITION BY affect window function behavior? For instance, with PARTITION BY region where some rows have region=NULL, explain how those rows are partitioned and potential pitfalls when computing per-region aggregates.

Sample Answer

In window functions PARTITION BY treats NULL like any other value when forming partitions: all rows where the partition expression evaluates to NULL go into the same partition. That means NULLs are grouped together rather than being treated as "different" each row.

Example:

SELECT region,
       SUM(sales) OVER (PARTITION BY region) AS region_sales
FROM sales_table;

All rows with region = NULL get the same region_sales (the sum of sales for the NULL partition).

Pitfalls and gotchas:- Implicit grouping of NULLs can hide data-quality issues: rows with missing region values are aggregated together, which may be unintended if NULL means "unknown" rather than a true category.- COUNT(column) vs COUNT(*) matters: COUNT(region) inside or outside a partition excludes NULLs, while COUNT(*) counts rows in the partition (including NULL region rows when you’re inside the NULL partition).- Aggregates like AVG ignore NULLs in the aggregated column; if many rows in a NULL partition have NULL values for the metric, results may be biased.- Joins/filters upstream that use IS NULL/IS NOT NULL can change partition membership unexpectedly.

Best practices:- Be explicit: map NULLs to a sentinel (COALESCE(region, 'UNKNOWN')) if you want them treated as a category you recognize.- Or filter out NULLs when you don’t want them included: WHERE region IS NOT NULL.- Check counts per partition (COUNT(*) vs COUNT(col)) to validate data completeness before relying on per-region metrics.

HardTechnical

61 practiced

Design a recursive CTE to propagate decayed influence in a follower graph such that each level contributes a fraction of the upstream score. Show how you would combine recursion with window functions to rank users by cumulative influence and discuss termination and performance considerations.

Sample Answer

To propagate decayed influence in a follower graph we can use a recursive CTE that starts from each source user with their base score and at each recursion step spreads a fraction (decay factor) of the current score to direct followers. Then aggregate scores per target and use window functions to rank by cumulative influence.

Example schema assumptions:- users(user_id)- follows(follower_id, followee_id) -- follower -> followee (i.e., follower subscribes to followee)- base_scores(user_id, base_score)

Approach:1. Seed: each user with their base_score at depth = 0.2. Recursion: from current node, push decayed_score * decay to each follower.3. Stop when decayed_score < epsilon or depth > max_depth to ensure termination.4. Aggregate per target (sum of contributions from all paths).5. Use window functions to rank users by cumulative influence and show breakdown by depth.

SQL (Postgres-compatible):

sql

WITH RECURSIVE influence_propagation AS (
  -- Seed: initial scores at depth 0 (self contribution)
  SELECT
    b.user_id AS source_id,
    b.user_id AS target_id,
    b.base_score AS contribution,
    0 AS depth
  FROM base_scores b

  UNION ALL

  -- Recursive step: propagate contribution to followers of current target
  SELECT
    ip.source_id,
    f.follower_id AS target_id,              -- follower receives influence from current target
    ip.contribution * :decay_factor AS contribution,
    ip.depth + 1 AS depth
  FROM influence_propagation ip
  JOIN follows f
    ON f.followee_id = ip.target_id
  WHERE ip.contribution * :decay_factor > :epsilon
    AND ip.depth < :max_depth
)

-- Aggregate and rank
, aggregated AS (
  SELECT
    target_id,
    SUM(contribution) AS total_influence,
    -- optional: influence breakdown by depth
    MAX(depth) FILTER (WHERE contribution IS NOT NULL) AS max_depth_seen
  FROM influence_propagation
  GROUP BY target_id
)

SELECT
  a.target_id,
  a.total_influence,
  RANK() OVER (ORDER BY a.total_influence DESC) AS influence_rank
FROM aggregated a
ORDER BY a.total_influence DESC
LIMIT 100;

Replace :decay_factor (e.g., 0.5), :epsilon (e.g., 1e-4), :max_depth (e.g., 10).

Key concepts / reasoning:- Decay factor guarantees contributions shrink each hop; combining with epsilon and max_depth prevents infinite recursion even if graph has cycles.- Using source_id in the recursion lets you trace contributions from origins; if you only need totals, you can drop source_id to reduce state.- Aggregation after recursion sums contributions from all paths. This captures multiple upstreams and multiple-hop effects.

Termination & correctness:- Recursive CTEs terminate when no new rows meet the WHERE clause. The product of repeated multiplication by decay_factor converges to zero; with epsilon cutoff and max_depth you ensure finite rows.- Beware cycles: without decay/epsilon, cycles cause infinite propagation. The WHERE clause and depth cap address this.

Performance considerations:- Explosion risk: dense graphs and many sources produce huge intermediate sets. Mitigations: - Limit seeds: only propagate from high base_score sources or sample sources. - Increase epsilon and reduce max_depth. - Aggregate intermediate results per (target, depth) to collapse duplicates — e.g., periodically aggregate inside recursion (database-dependent). - Use materialized intermediate tables / batching instead of a single huge CTE. - Add indexes on follows(followee_id) and base_scores(user_id).- Memory/CPU: recursive CTEs are often memory-heavy. If scale is large, consider graph engines (e.g., GraphFrames, Neo4j) or iterative MapReduce/Spark where you can control partitioning.

Alternatives:- Matrix power / linear algebra: adjacency matrix times score vector with geometric series (good when graph fits in memory).- Probabilistic influence approximation using Monte Carlo simulation.- Push-based iterative algorithm in Spark/Gelly or Pregel-like systems for massive graphs.

Edge cases:- Self-follow edges: handled via decay; if not desired, exclude them.- Parallel paths: allowed; contributions sum.- Very small base_scores: filter early to reduce work.

This pattern gives an interpretable, SQL-native way to compute decayed influence and rank users, with explicit knobs (decay, epsilon, max_depth) to balance accuracy and performance.

HardTechnical

61 practiced

As part of feature engineering for a churn prediction model, outline a multi-step SQL pipeline (using CTEs and window functions) that produces for each user: last 7-day average usage, days-since-last-activity, number of distinct event types in last 30 days, and a 3-point slope of recent usage trend. Include explanation of window frames and partition choices.

Sample Answer

Situation: We need reproducible, in-SQL feature engineering that for each user computes:- last 7-day avg usage- days-since-last-activity- number of distinct event types in last 30 days- a 3-point slope of recent usage trend

Below is a compact multi-CTE pipeline using window functions and clear partitioning. Assumptions: source table events(user_id, event_time TIMESTAMP, event_type, usage FLOAT). model_date is the evaluation date (can be max(event_time) or a fixed batch date).

sql

WITH
-- 1. determine reference date per user (end of observation window)
user_ref AS (
  SELECT
    user_id,
    MAX(event_time)                AS ref_ts,
    DATE(MAX(event_time))          AS ref_date
  FROM events
  GROUP BY user_id
),

-- 2. daily aggregates for each user for the 30-day window before ref_date
daily AS (
  SELECT
    e.user_id,
    DATE(e.event_time)                     AS day,
    SUM(e.usage)                           AS day_usage,
    COUNT(DISTINCT e.event_type) OVER (PARTITION BY e.user_id, DATE(e.event_time)) AS distinct_types_day
  FROM events e
  JOIN user_ref r
    ON e.user_id = r.user_id
   AND DATE(e.event_time) BETWEEN DATE_SUB(r.ref_date, INTERVAL 29 DAY) AND r.ref_date
  GROUP BY e.user_id, DATE(e.event_time)
),

-- 3. ensure every day in window exists (fill missing days with zero usage)
calendar AS (
  SELECT user_id, ref_date, seq.day
  FROM user_ref
  CROSS JOIN (
    SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
    UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
    UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
    UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
  ) AS seq(d)
  CROSS APPLY (SELECT DATE_SUB(ref_date, INTERVAL seq.d DAY) AS day)
),

daily_full AS (
  SELECT
    c.user_id,
    c.day,
    COALESCE(d.day_usage, 0) AS day_usage,
    COALESCE(d.distinct_types_day, 0) AS distinct_types_day
  FROM calendar c
  LEFT JOIN daily d
    ON c.user_id = d.user_id AND c.day = d.day
),

-- 4. compute rolling features using window frames partitioned by user ordered by day
features_per_day AS (
  SELECT
    user_id,
    day,
    day_usage,

    -- last 7-day average usage (inclusive of current day)
    AVG(day_usage) OVER (
      PARTITION BY user_id
      ORDER BY day
      RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS avg_7d_usage,

    -- count distinct event types over last 30 days: sum of per-day distinct_types_day over window
    SUM(distinct_types_day) OVER (
      PARTITION BY user_id
      ORDER BY day
      RANGE BETWEEN INTERVAL 29 DAY PRECEDING AND CURRENT ROW
    ) AS distinct_event_types_30d,

    -- for 3-point slope: take day_usage at day (t), t-1, t-2 and compute slope = (y_t - y_{t-2}) / 2
    LAG(day_usage, 1) OVER (PARTITION BY user_id ORDER BY day) AS y_t1,
    LAG(day_usage, 2) OVER (PARTITION BY user_id ORDER BY day) AS y_t2
  FROM daily_full
),

-- 5. pick only rows corresponding to each user's ref_date (end of window) to produce final features
final_features AS (
  SELECT
    u.user_id,
    f.day                         AS ref_date,
    f.avg_7d_usage,
    -- days since last activity: difference between ref_ts and last actual event_time
    DATE_DIFF(u.ref_date, MAX(e.event_time), DAY) OVER (PARTITION BY u.user_id) AS days_since_last_activity,
    f.distinct_event_types_30d,
    -- 3-point slope (average daily change over last 2-day span). If y_t2 is NULL, set NULL.
    CASE
      WHEN f.y_t2 IS NULL THEN NULL
      ELSE (f.day_usage - f.y_t2) / 2.0
    END AS slope_3point
  FROM user_ref u
  JOIN features_per_day f
    ON u.user_id = f.user_id AND u.ref_date = f.day
  LEFT JOIN events e
    ON e.user_id = u.user_id AND DATE(e.event_time) = u.ref_date
  GROUP BY u.user_id, f.day, f.avg_7d_usage, f.distinct_event_types_30d, f.day_usage, f.y_t2
)

SELECT * FROM final_features;

Key reasoning and window/frame choices:- Partition BY user_id isolates per-user time series (no leakage across users).- ORDER BY day with RANGE BETWEEN INTERVAL n DAY PRECEDING is used because windows are time-based (handles missing days transparently when we expanded calendar).- AVG over 7-day RANGE ensures we average the last 7 calendar days ending at ref_date.- For distinct event-types in 30 days we sum per-day distinct counts over a 30-day RANGE (acceptable if event types don't repeat within day; else use APPROX_COUNT_DISTINCT over raw events partitioned).- 3-point slope uses lag 1 and 2 to get last 3 consecutive days; slope = (y_t - y_{t-2})/2 approximates linear trend. If you need robust slope, run an OLS over last k points using windowed SUMs (Σx, Σy, Σxy, Σx2).- days-since-last-activity computed against last event_time per user (use ref_ts vs last activity timestamp).

Edge considerations: timezone normalization, sparse activity (NULL handling), large cardinality — pre-aggregate events and use partitioning/clustered storage for performance.

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.