Explain cohort retention and write a pseudo-query for May signups

WHAT THIS TESTS: Whether you understand that cohort retention tracks groups of users who start together and measures their return rate over specific relative time windows, as opposed to aggregate metrics like DAU or MAU that blend user vintages and mask drop-off curves. It also tests your ability to model SQL date arithmetic, handle the denominator problem in retention calculations, and choose the right join type to include inactive users.

A GOOD ANSWER COVERS: First, define the cohort as all distinct users with a signup_date falling in May. Second, explain why aggregate active-user counts are misleading: if June signups surge, aggregate MAU can rise even while May users churn, making the product look healthier than it is. Third, outline a pseudo-query structure: start with a users table containing user_id and signup_date, filter to May signups, then left-join an activity table on user_id where event_date is strictly greater than signup_date. Compute relative age as datediff between event_date and signup_date. Bucket into Week 1 for ages 1 through 7 inclusive, Week 2 for ages 8 through 14 inclusive, and Week 4 for ages 22 through 28 inclusive. Fourth, emphasize that the denominator is the count of distinct users in the May cohort, and the numerator is distinct returning users in each bucket, so users with no activity still count as zero and do not disappear from the denominator.

COMMON WRONG ANSWERS: Using calendar weeks or months instead of periods relative to each individual signup date, which destroys the cohort concept and makes comparisons unfair. Writing an inner join between users and activity, which silently drops zero-activity users and inflates retention. Defining retention as total events divided by total users rather than distinct returners divided by cohort size. Forgetting to deduplicate multiple events per user per day, which can overstate engagement. Choosing an ambiguous activity timestamp that conflates signup with first action.

LIKELY FOLLOW-UPS: How would you define active: any event or a specific value moment? How do you handle timezone differences between signup and event timestamps? How would you compare the May cohort to the June cohort? How would you turn this query into a retention matrix for every weekly cohort?

ONE CONCRETE EXAMPLE: Imagine 2,000 users signed up in May. Of those, 800 performed an action within 1 to 7 days after signup, 600 within 8 to 14 days, and 400 within 22 to 28 days. The pseudo-query would return 40 percent Week 1 retention, 30 percent Week 2 retention, and 20 percent Week 4 retention. Notice that the denominator stays fixed at 2,000 while the numerators shrink, revealing the true decay curve.

Source: amplitude.com