How to query a monthly cohort retention table in SQL?
Tests your ability to translate a core business metric into a multi-step SQL query. A good answer finds each user's acquisition month, joins that back to their activity, and pivots the data into a cohort grid. A red flag is calculating aggregate retention.
What's really being asked
This question tests your practical SQL skills for a common, non-trivial analytics task. It's not about obscure syntax but about your ability to structure a complex query logically. Interviewers are looking for your ability to break down a business request ('show me retention') into concrete data manipulation steps: identifying cohorts, measuring subsequent activity, and structuring the final output. It's a test of data modeling on the fly.
The full answer
A strong answer outlines a clear, multi-step process, often using Common Table Expressions (CTEs). First, identify the cohort for each user. This is their sign-up month or the month of their first event, found with MIN(timestamp) grouped by user_id. Second, calculate the 'month number' for every subsequent event. This involves joining the user's cohort month back to the full events table and calculating the difference in months between the event timestamp and the cohort month. Third, aggregate the results. This final step involves a GROUP BY on the cohort month and the month number, with a COUNT(DISTINCT user_id) to get the number of active users for that period. The final output should be structured like a pivot table: rows are cohort months, columns are months since acquisition (Month 0, Month 1, etc.), and values are the count of retained users.
The mistakes people make
The most common mistake is to describe an aggregate retention calculation instead of a cohort-based one. For example, calculating the percentage of January's active users who were also active in February. This is wrong because it mixes users acquired in January with users acquired in all previous months. This can hide serious issues, like poor retention of new users, because older, more loyal users can mask the churn of newer ones. Another red flag is fumbling the logic for calculating the 'month number' or struggling to explain how to pivot the data from a long format (user_id, cohort_month, activity_month) to the wide grid format.
What usually comes next
Be ready for 'How would you turn this into percentages?' (Divide each month's count by the initial cohort size, which is the count for Month 0). Another is 'What if the events table is billions of rows? How would you optimize this?' (Discuss pre-aggregation, creating daily/monthly summary tables, using approximate counting functions like APPROX_COUNT_DISTINCT, or partitioning). A third follow-up could be about defining 'active' – does any event count, or only specific ones like 'login' or 'purchase'?
A concrete example
Let's say 1,000 users first appeared in January 2023 (the Jan cohort). Your query should first establish this cohort size of 1,000 as the 'Month 0' value. Then, you'd query how many of those specific 1,000 users had an event in February 2023. If 500 of them did, the 'Month 1' retention for the January cohort is 50%. If 400 of that same initial group had an event in March 2023, 'Month 2' retention is 40%. You repeat this for each cohort (Feb, Mar, etc.) to build the full table.
Interview question
What is the primary analytical flaw of calculating aggregate retention (e.g., % of February's users also active in January) instead of using a cohort-based approach?
- a.It mixes loyal old users with churning new users, which can hide poor retention trends.Correct
- b.It inaccurately defines an 'active user' by counting any event instead of specific ones.
- c.It fails to track user activity beyond a single month-over-month period.
- d.It requires more complex and less performant SQL queries than cohort analysis.
Why? this is the answer
Cohort analysis isolates users by acquisition date, revealing trends for specific groups. Aggregate retention mixes all users, so high loyalty from old users can mask problems with retaining new ones.
Just read this? Test yourself on what you have been reading.
Read the original → stratascratch.com
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