Explain cohort analysis for user retention and write a pseudo-query

What's really being asked

This question tests your ability to think about metrics with precision, not just recite definitions. Interviewers want to see if you understand that aggregate metrics (like overall DAU/MAU) can be misleading. Cohort analysis demonstrates your ability to isolate variables (like a product change or marketing campaign) and measure their true impact on user behavior over time. It's a proxy for data-driven thinking and business acumen.

The full answer

A strong answer has four parts. First, define a cohort as a group of users who share a common characteristic, usually an acquisition date (e.g., all users who signed up in May 2024). Second, explain why this is valuable: it isolates the group from future growth, so you can see the true retention curve of that specific group, unaffected by new signups. This helps diagnose problems, like if a feature change in June hurt retention for the June cohort compared to the May cohort. Third, outline the calculation steps: identify the cohort, count its initial size, then for each subsequent period (Week 1, Week 2), count how many of the original users were active, and divide by the initial size. Fourth, provide a clear pseudo-query that reflects this logic.

The mistakes people make

The most common red flag is confusing cohort retention with aggregate retention. A weak answer might say "we had 80% retention last week," which is meaningless without a cohort. This usually means they calculated (Last Week's Active Users / This Week's Active Users), which is wrong. Another mistake is writing a query that doesn't correctly link a user's activity back to their specific signup cohort. For example, a query that just counts active users in a given week without joining back to a users table on signup_date.

What usually comes next

Expect questions like: "How would you visualize this data?" (A cohort grid or triangle chart). "What other types of cohorts are useful besides acquisition cohorts?" (Behavioral cohorts, like users who first used Feature X). "Your Week 4 retention for the May cohort is 15%, down from 25% for the April cohort. What are your first three steps to investigate?" (Check for product releases, marketing campaign changes, or external events between April and May).

A concrete example

Let's say 10,000 users signed up in the first week of May. In the second week of May (Week 1), 4,000 of those original 10,000 users logged in again. Your 1-week retention is 4,000 / 10,000 = 40%. In the third week of May (Week 2), only 3,000 of the original 10,000 users were active. Your 2-week retention is 3,000 / 10,000 = 30%. The denominator is always the initial cohort size.

Pseudo-query

WITH may_cohort AS ( SELECT user_id, signup_date FROM users WHERE signup_date BETWEEN '2024-05-01' AND '2024-05-31' ),

activity_by_week AS (

Select

user_id, FLOOR(DATEDIFF(activity_date, signup_date) / 7) AS week_number FROM activity JOIN may_cohort USING(user_id) WHERE activity_date >= signup_date GROUP BY 1, 2 )

Select

(SELECT COUNT(DISTINCT user_id) FROM activity_by_week WHERE week_number = 1) * 1.0 / COUNT(DISTINCT user_id) AS week_1_retention, (SELECT COUNT(DISTINCT user_id) FROM activity_by_week WHERE week_number = 2) * 1.0 / COUNT(DISTINCT user_id) AS week_2_retention, (SELECT COUNT(DISTINCT user_id) FROM activity_by_week WHERE week_number = 4) * 1.0 / COUNT(DISTINCT user_id) AS week_4_retention FROM may_cohort;