Write SQL to generate a monthly cohort retention table from raw events

WHAT THIS TESTS: This question evaluates whether you can reason about time-series user behavior in SQL without relying on BI tools. The interviewer cares about three specific mechanics: using window functions like MIN OVER or a self-join to isolate a user's acquisition month, truncating timestamps to a regular period such as month, and normalizing counts into percentages. It also tests whether you understand the business difference between aggregate retention and cohort retention, because mixing users from different acquisition periods hides churn patterns.

A GOOD ANSWER COVERS: First, derive the cohort month for every user by truncating the earliest timestamp to month. Second, create an activity map that pairs each user with every month they performed an event. Third, join the cohort table back to the activity table on user_id and compute the period difference in months between the cohort month and the activity month. Fourth, aggregate with COUNT DISTINCT user_id grouped by cohort month and period number, then divide by the total cohort size to get a retention rate. A clean answer mentions using DATE_TRUNC or equivalent, and explains why a self-join or conditional aggregation is needed to fill zero-activity months.

COMMON WRONG ANSWERS: A major red flag is writing a query that simply counts active users month over month without assigning cohorts, which produces an aggregate retention figure that blends old and new users. Another mistake is using COUNT instead of COUNT DISTINCT, inflating retention when a single user fires many events. Some candidates forget to handle the denominator correctly by using the full cohort size rather than the shrinking pool of previously active users, which actually measures survival rather than classic cohort retention. Failing to account for timezone or calendar-month boundaries is also a subtle error.

LIKELY FOLLOW-UPS: The interviewer may ask how you would handle weekly or daily granularity instead of monthly, which changes the truncation and join logic but not the structure. They might ask how to distinguish between any-event retention and feature-specific retention, requiring a filter on event_name. Another common extension is calculating rolling retention versus fixed-period retention, or asking how to optimize the query when the events table has billions of rows, which pushes the conversation toward partitioning, indexing, or pre-aggregated tables.

ONE CONCRETE EXAMPLE: Imagine ten users who all sign up in January 2024. Three return in February, two in March, and one in April. Your query should output a row for the January cohort with period zero showing one hundred percent, period one showing thirty percent, period two showing twenty percent, and period three showing ten percent. If you had instead calculated aggregate retention for February across all existing users including December signups, the denominator would be polluted and the metric would hide that newer January users are churning faster than older ones.

Source: StrataScratch