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 the simplest clear approach, explain the trade-off, then move toward the cleaner answer.
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 |