Window Functions

What's being tested

These problems test window-function analytics over customer, order, driver, restaurant, and city-level event data. You need to partition entities, order events in time, compare current rows to prior rows, rank within groups, and combine row-level windows with aggregations without losing analytical meaning.

Patterns & templates

• Prior-event comparison — use LAG(value) OVER (PARTITION BY customer_id ORDER BY order_ts) to compute spend changes, reorder gaps, or previous restaurant/city.

• Next-event comparison — use LEAD(status) OVER (...) when analyzing driver request sequences, acceptance funnels, or what happened after a dispatch attempt.

• Ranking within groups — use ROW_NUMBER, RANK, or DENSE_RANK with PARTITION BY city_id / restaurant_id; choose based on tie behavior.

• Top-N per segment — wrap window output in a CTE, then filter WHERE rn <= N; avoid filtering window aliases in the same SELECT.

• Running totals and rolling metrics — use SUM(amount) OVER (PARTITION BY user_id ORDER BY order_ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) for cumulative spend.

• Percentile segmentation — use NTILE(4), PERCENT_RANK, or CUME_DIST for quartiles; validate whether equal-sized buckets or true percentile thresholds are intended.

• Aggregate then window — first group to restaurant/day/customer level, then apply windows; mixing raw order rows with grouped metrics often duplicates revenue.

Common pitfalls

Pitfall: Using RANK when the interviewer expects exactly one row per group; use ROW_NUMBER with a deterministic tie-breaker like order_id.

Pitfall: Forgetting that window functions run after WHERE, so filtering dates or statuses too early can remove rows needed for prior-event comparisons.

Pitfall: Ordering only by date when multiple orders occur on the same day; include timestamp and stable IDs to make results reproducible.

Practice these

The practice cards below cover the canonical variants — solve all of them and time yourself.