SQL Window Functions And Analytics

What's being tested

Tests SQL windowing for product analytics: cohort metrics, rolling time-series summaries, deduped event funnels, top-N segmentation, and experiment readouts. Uber DS interviews probe whether you can turn messy trip/user/event tables into defensible metrics without double-counting users, leaking future data, or mishandling time boundaries.

Patterns & templates

• Last/first event per entity — ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts DESC, event_id DESC); filter rn = 1 for deterministic deduping.

• Rolling metrics — AVG(metric) OVER (PARTITION BY city ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW); use ROWS for fixed row counts.

• Rolling percentiles — PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY eta) over a 7-day frame; check warehouse support and NULL handling.

• Cohort conversion / CTR — define denominator once, dedupe exposures/clicks with COUNT(DISTINCT user_id), and join within explicit windows like click_ts <= impression_ts + INTERVAL '48 hours'.

• Top-N ranking — RANK, DENSE_RANK, or ROW_NUMBER depending on tie behavior; always state whether tied promos/drivers/users should both appear.

• Date spine joins — generate all dates, left join events, COALESCE missing counts to zero; needed for rolling averages and anomaly detection.

• Timezone-aware truncation — convert to local market time before DATE_TRUNC; SF January metrics should not use raw UTC day boundaries.

Common pitfalls

Pitfall: Using COUNT(*) after joining impressions to clicks inflates CTR when users click multiple times; dedupe at the user-impression grain first.

Pitfall: Computing rolling conversion with future rows, e.g. ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING, leaks information into historical metrics.

Pitfall: Treating RANK and ROW_NUMBER as interchangeable causes silent tie bugs in top-N promotion or marketplace behavior analyses.

Practice these

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