SQL Cohort, Retention, And Churn Analysis

What's being tested

Demonstrates manipulation of SQL event-level logs to construct cohort analysis, compute retention and churn, and produce per-variant aggregates. Interviewer probes deduplication, time-windowing, correct cohort alignment, and clear assumptions about first-touch vs. triggered cohorts.

Patterns & templates

• ROW_NUMBER() for deduplication — use ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts) to pick first/last event per user.

• Window functions for rolling/cohort flags — SUM(...) OVER (PARTITION BY cohort ORDER BY day) or MAX(...) to propagate cohort membership.

• Date arithmetic for retention windows — DATEDIFF(day, signup_date, event_date) BETWEEN 0 AND 30 for D30 retention; watch timezone.

• COUNT(DISTINCT ...) for unique-user metrics; prefer approximate (hyperloglog) only for very large cardinalities.

• Anti-join / LEFT JOIN ... WHERE null to compute churn/exclusion (users who didn’t convert or perform event).

• Intent-to-treat vs triggered — define the denominator: all assigned users vs. only those who received exposure; implement with pre-filtering or flags.

• Aggregate by variant/date — group by experiment_variant, cohort_date and compute rates with SUM(events)/COUNT(users); include NULL-handling.

Common pitfalls

Pitfall: Counting events instead of unique users — leads to inflated retention; always dedupe on user-level before aggregating.

Pitfall: Misaligned cohort windows (using event_date instead of signup_date) — yields wrong D30 membership and biased churn.

Pitfall: Ignoring late-arriving events or timezone differences — clarify event-time semantics and apply AT TIME ZONE or consistent normalization.

Practice these

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