Monthly Customer Retention Rate
Preview mode. Log in to edit, run, submit, and save progress.
Description
You are given a table of customer purchases. Write a SQL query to compute, for each month: the number of distinct active customers in that month (active_customers), and the number of those customers who also made a purchase in the immediately following month (retained_next_month). Return month, active_customers, and retained_next_month ordered by month. Table: Purchases
| Column Name | Type | Description |
|---|---|---|
| id | INT | Primary key |
| customer_id | INT | ID of the customer |
| purchase_date | DATE | Date of the purchase |
Database Schema (Inferred)
Purchases
| Column Name | Example Value |
|---|---|
| id | 1 |
| customer_id | 1 |
| purchase_date | 2023-01-05 |
Example
Purchases
| id | customer_id | purchase_date |
|---|---|---|
| 1 | 1 | 2023-01-05 |
| 2 | 2 | 2023-01-10 |
| 3 | 3 | 2023-01-20 |
| 4 | 1 | 2023-02-08 |
| 5 | 2 | 2023-02-15 |
| 6 | 1 | 2023-03-01 |
| 7 | 4 | 2023-03-10 |
| 8 | 5 | 2023-04-01 |
Output
| month | active_customers | retained_next_month |
|---|---|---|
| 2023-01 | 3 | 2 |
| 2023-02 | 2 | 1 |
| 2023-03 | 2 | 0 |
| 2023-04 | 1 | 0 |
Explanation:
Build a distinct (customer_id, month) CTE. Then LEFT JOIN it to itself shifted by one month to count customers who appear in both the current and next month.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
Purchases
| id | customer_id | purchase_date |
|---|---|---|
| 1 | 1 | 2023-01-05 |
| 2 | 2 | 2023-01-10 |
| 3 | 3 | 2023-01-20 |
| 4 | 1 | 2023-02-08 |
| 5 | 2 | 2023-02-15 |
| 6 | 1 | 2023-03-01 |
| 7 | 4 | 2023-03-10 |
| 8 | 5 | 2023-04-01 |
Output
| month | active_customers | retained_next_month |
|---|---|---|
| 2023-01 | 3 | 2 |
| 2023-02 | 2 | 1 |
| 2023-03 | 2 | 0 |
| 2023-04 | 1 | 0 |