SQL interview question: Cohort Retention Analysis
Monthly Signup Cohort Retention
Growth team needs a cohort analysis: group customers by signup month, then track what % placed an order in subsequent months.
Return signup_cohort (YYYY-MM), months_since_signup (whole 30-day blocks since the first day of the signup month: 0, 1, 2...), and active_pct (% of cohort active that month, rounded to 1 decimal).
Skills: advanced date arithmetic with cohort tracking
Tables
customers 12 rows
- customer_id integer PK
- name text
- email text
- city text
- signup_date text
| customer_id | name | city | signup_date | |
|---|---|---|---|---|
| 101 | Alice Mitchell | [email protected] | San Francisco | 2024-01-15 |
| 102 | Bob Chen | [email protected] | New York | 2024-02-20 |
| 103 | Carol Davis | [email protected] | Los Angeles | 2024-03-10 |
orders 15 rows
- order_id integer PK
- customer_id integer → customers
- order_date text
- total_amount real
- status text
| order_id | customer_id | order_date | total_amount | status |
|---|---|---|---|---|
| 1 | 101 | 2024-01-20 | 249.99 | delivered |
| 2 | 101 | 2024-03-15 | 1999.99 | delivered |
| 4 | 101 | 2024-09-05 | 79.99 | delivered |