Explain cohort analysis and write a retention query

What's really being asked

This question assesses your data literacy and practical analytics skills. It's not just about knowing the definition of "cohort." Interviewers want to see if you understand why cohort analysis is superior to aggregate metrics for understanding user behavior. They are testing your ability to think in terms of user groups, time-series analysis, and how to translate that logic into a data query. It separates candidates who can recite definitions from those who can actually measure product health.

The full answer

A strong answer has three parts. First, define a cohort as a group of users who share a common characteristic over a specific time period, like signing up in May. Second, explain its value: cohort analysis isolates variables. It shows if product changes improve retention for new users, something hidden by aggregate metrics that average old, loyal users with new ones. Third, outline the pseudo-query. This involves selecting the signup month/week as the cohort, then joining a user table with an activity/events table. The core logic is to count the distinct users from the May cohort who also have an activity record in week 1, week 2, and week 4 post-signup.

The mistakes people make

A major red flag is confusing cohort retention with aggregate retention. For example, saying "we retained 80% of our users this month" is an aggregate metric. A cohort answer would be "the May signup cohort had a 4-week retention of 25%." Another common mistake in the query is failing to properly define the time windows. For example, calculating week 2 retention by just looking at the second week of May, rather than the second week after each user's individual signup date. The query must be relative to the user's start date. Getting the logic wrong is the critical failure.

What usually comes next

Be ready for "How would this change for rolling retention vs. classic retention?". Or, "Imagine our retention for the May cohort is 10% lower than April's. What are the first three things you would investigate?". Another likely question is about tooling: "How would you build a dashboard for this? What tools would you use and why?". They might also ask you to write the query in a specific SQL dialect.

A concrete example

A pseudo-query would first create a temporary table (or CTE) of users who signed up in May. Let's call it may_signups. Then, you would query this may_signups table, joining it to a user_activity table on user_id. The main query would select conditional counts. For 1-week retention, you'd count distinct users where the activity date is between 7 and 14 days after their individual signup date. You'd do the same for 2 weeks (14-21 days) and 4 weeks (28-35 days). Finally, you divide these counts by the total number of users in the may_signups cohort to get the retention percentages.