Task
ShopKart wants to measure how many shoppers who signed up in January came back and placed another order later. Write a SQL query to calculate the retention rate for the January signup cohort.
Requirements
- Consider only shoppers whose
signup_date is in January 2024.
- A shopper is retained if
has_returned is TRUE.
- Return the total January signups, retained January shoppers, and the retention rate as a percentage rounded to 2 decimal places.
Table Definition
shoppers
| column | type | description |
|---|
| shopper_id | INT | Unique shopper ID |
| shopper_name | VARCHAR(100) | Shopper name |
| signup_date | DATE | Date the shopper created an account |
| has_returned | BOOLEAN | Whether the shopper returned after signup |
| country | VARCHAR(50) | Shopper country |
Sample Data
| shopper_id | shopper_name | signup_date | has_returned | country |
|---|
| 1 | Ava Patel | 2024-01-15 | TRUE | US |
| 2 | Liam Chen | 2024-01-03 | FALSE | CA |
| 3 | Noah Kim | 2024-02-01 | TRUE | US |
| 4 | Emma Davis | 2024-01-31 | TRUE | UK |
| 5 | Olivia Brown | 2023-12-31 | TRUE | US |
| 6 | Sophia Wilson | 2024-01-10 | NULL | AU |
| 7 | Mason Lee | 2024-01-22 | FALSE | US |
| 8 | Isabella Garcia | 2024-03-05 | TRUE | CA |
Expected Output
| january_signups | retained_shoppers | retention_rate_pct |
|---|
| 7 | 4 | 57.14 |
