Generate a monthly cohort retention table from user events
This tests your ability to translate a business metric into a multi-step SQL query. A great answer defines cohorts by first activity, maps subsequent activity to period indices, counts distinct users, and pivots the result.
What's really being asked
This question tests your ability to translate a core business metric into a multi-step data transformation using SQL. The interviewer is evaluating your logical decomposition skills, not just your knowledge of SQL syntax. They want to see if you can break down the problem into logical stages: identifying user cohorts, tracking their subsequent activity over time, and structuring the final output into the classic retention matrix. This demonstrates proficiency with CTEs, window functions, date manipulation, and conditional aggregation.
The full answer
A great answer outlines a clear, four-step process. First, define the cohorts by creating a CTE that finds the first seen timestamp for each user_id, typically using MIN(timestamp) grouped by user_id. This timestamp, truncated to the month, is the user's cohort. Second, join this cohort information back to the main events table to map all user activities. For each activity, calculate the time difference (e.g., "month number") between the activity month and the user's cohort month. Third, aggregate the results by grouping on the cohort month and the calculated month number, using COUNT(DISTINCT user_id) to get the number of active users for that period. Fourth, explain how to pivot this data into a matrix, with cohort months as rows and month numbers (Month 0, Month 1, etc.) as columns, using conditional aggregation with CASE statements.
The mistakes people make
The most common red flag is calculating aggregate retention instead of cohort retention. This happens when a candidate simply counts active users month-over-month without bucketing them by their acquisition date. This answer completely misses the core of the question. Another mistake is forgetting to use COUNT(DISTINCT user_id), which leads to over-counting users who have multiple events in a single period. Finally, senior candidates are expected to explain the pivot step clearly, not just say "then you pivot." Failing to articulate the conditional aggregation logic (e.g., using CASE statements) is a sign of weaker practical skills.
What usually comes next
Expect follow-ups on optimization and definition. For example: "This events table has 50 billion rows. How do you make this query performant?" (Answer: pre-aggregated daily/monthly activity tables). Or, "How would you calculate the retention percentage?" (Answer: divide each month's count by the 'Month 0' count for that cohort). Another common one is, "What if 'active' means firing a 'purchase' event, not just any event?" (Answer: add a WHERE event_name = 'purchase' clause in the relevant CTEs).
A concrete example
A user's first event is on January 15, 2023. They belong to the '2023-01' cohort. Their activity in January counts towards 'Month 0' for this cohort. If they have another event on February 20, 2023, they are counted as a retained user in 'Month 1' for the '2023-01' cohort. If 10,000 users joined in January and 4,500 of them had an event in February, the Month 1 retention for the January cohort is 45%.
Interview question
When calculating monthly cohort retention, what is the fundamental purpose of identifying a user's "cohort month" as their first activity month?
- a.To establish a baseline for each user group, allowing tracking of their continued engagement relative to their initial acquisition.Correct
- b.To segment users into groups based on their most recent activity for targeted marketing campaigns.
- c.To determine the overall number of unique active users in any given calendar month.
- d.To efficiently filter out inactive users from the dataset before performing further aggregations.
Why? this is the answer
The card emphasizes that cohort retention involves defining cohorts by a user's first activity and then tracking their subsequent activity over time relative to that initial period. This allows for measuring engagement from their acquisition point. Option C describes aggregate retention, which the card identifies as a common misconception that "completely misses the core of the question".
Just read this? Test yourself on what you have been reading.
Read the original → stratascratch.com
- #sql
- #analytics
- #metrics
- #data modeling
- #retention
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on sql — each one lists the topics its interview covers.
See open roles