Context

Cohort retention is a common product analytics pattern used to measure how well users return after signup or first activity. Interviewers ask this to evaluate whether you can translate a business metric into a clean SQL workflow.

Core question

Explain how you would structure a SQL analysis to measure retention by cohort. Your answer should cover:

1. How to define the cohort date for each user
2. How to identify retained users in later periods
3. How to calculate the period offset from the cohort start
4. How to aggregate results into a cohort retention table or matrix
5. How to handle edge cases such as multiple events in the same period, missing activity, and partial current periods

Scope guidance

Focus on the SQL design and reasoning rather than a single exact query. The interviewer expects a practical explanation of the steps, common table structure, and the main pitfalls that affect correctness.