Task
ShopKart wants to measure how many shoppers return in the following week after making a purchase. Write a SQL query to calculate week-over-week shopper retention.
A shopper is considered retained in week W if they made at least one completed order in week W and also made at least one completed order in week W + 1.
Requirements
- Use only completed orders.
- Aggregate activity by shopper and calendar week using
DATE_TRUNC('week', order_date).
- For each week, return:
- the week start date
- the number of active shoppers in that week
- the number of those shoppers who returned the next week
- the retention rate as
retained_shoppers / active_shoppers, rounded to 4 decimals
- Exclude the final week in the dataset if there is no following week to evaluate retention against.
- Order results by week start date ascending.
Tables
shoppers
| column | type | description |
|---|
| shopper_id | INT | Unique shopper ID |
| shopper_name | VARCHAR(100) | Shopper name |
| signup_date | DATE | Date the shopper registered |
| region | VARCHAR(50) | Shopper region |
orders
| column | type | description |
|---|
| order_id | INT | Unique order ID |
| shopper_id | INT | Shopper who placed the order |
| order_date | DATE | Order date |
| order_status | VARCHAR(20) | Order status |
| order_total | NUMERIC(10,2) | Order amount |
Sample data
shoppers
| shopper_id | shopper_name | signup_date | region |
|---|
| 1 | Ava Patel | 2024-12-20 | East |
| 2 | Liam Chen | 2024-12-22 | West |
| 3 | Noah Kim | 2024-12-28 | South |
| 4 | Emma Diaz | 2025-01-02 | North |
orders
| order_id | shopper_id | order_date | order_status | order_total |
|---|
| 101 | 1 | 2025-01-06 | completed | 45.00 |
| 102 | 1 | 2025-01-13 | completed | 30.00 |
| 103 | 2 | 2025-01-07 | completed | 25.00 |
| 104 | 3 | 2025-01-14 | cancelled | 60.00 |
| 105 | 4 | 2025-01-20 | completed | 80.00 |
Expected output
| week_start | active_shoppers | retained_shoppers | retention_rate |
|---|
| 2025-01-06 | 3 | 2 | 0.6667 |
| 2025-01-13 | 3 | 2 | 0.6667 |
| 2025-01-20 | 3 | 1 | 0.3333 |
