SQL Window Functions, Cohorts, And Retention

What's being tested

Two skills: the ability to translate product questions into cohort-based SQL analyses and to implement them using window functions and date-bucketing so you can produce accurate cohort and retention metrics. Interviewers probe whether you know the canonical SQL idioms (first-touch cohorting, deduping, time-delta joins) and can avoid common analytic mistakes that mislead product decisions.

Patterns & templates

• ROW_NUMBER(): ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts) to pick each user's first or last event; ties broken by stable secondary key.

• First-touch cohort: use MIN(event_ts) OVER (PARTITION BY user_id) or ROW_NUMBER=1 then DATE_TRUNC('week', first_event_ts) to assign cohorts.

• Retention matrix: left-join cohort users to subsequent events on user_id and compute DATEDIFF/date_diff into discrete periods, then COUNT(DISTINCT user_id) per (cohort, period).

• LAG() / LEAD(): detect returns or churn by comparing consecutive events per user; LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date).

• Cumulative vs periodic: use SUM(active_flag) OVER (PARTITION BY cohort ORDER BY period ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) for cumulative retention.

• Time bucketing: prefer DATE_TRUNC('day'|'week'|'month', ts) and be explicit about week-start and timezone to avoid cohort drift.

• Performance: pre-aggregate events to daily active per user before heavy window ops; O(events) scanning but window ops can add memory pressure.

Common pitfalls

Pitfall: Counting COUNT(*) instead of COUNT(DISTINCT user_id) inflates retention by double-counting multiple events per user.

Pitfall: Assigning cohorts by last event or arbitrary event instead of first-touch misattributes acquisition and retention.

Pitfall: Ignoring timezone / week-start differences yields cohort leakage across boundaries and inconsistent trends.

Practice these

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