Task
BrightCart wants to compare user retention across acquisition channels. Write a SQL query to calculate day-30 retention by acquisition channel for users who signed up in January 2024.
A user is considered retained if they have at least one session on or after 30 days from signup and before 60 days from signup.
Requirements
- Use each user's acquisition channel from the
users table.
- Restrict the cohort to users whose
signup_date is between 2024-01-01 and 2024-01-31.
- For each channel, return:
- total users in the cohort
- retained users
- retention rate as a percentage rounded to 2 decimals
- Include channels with zero retained users.
- Order results by retention rate descending, then channel name ascending.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Primary key for each user |
| signup_date | DATE | Date the user created an account |
| acquisition_channel | VARCHAR(50) | Marketing channel attributed at signup |
| country_code | VARCHAR(2) | User country |
| | |
sessions
| column | type | description |
|---|
| session_id | INT | Primary key for each session |
| user_id | INT | User who started the session |
| session_date | DATE | Date of the session |
| device_type | VARCHAR(20) | Device used for the session |
| | |
Sample Data
users
| user_id | signup_date | acquisition_channel | country_code |
|---|
| 1 | 2024-01-05 | Paid Search | US |
| 2 | 2024-01-10 | Organic Search | CA |
| 3 | 2024-01-15 | Paid Social | US |
| 4 | 2024-01-20 | Referral | GB |
| 5 | 2024-01-25 | Paid Search | US |
| 6 | 2024-02-01 | Organic Search | US |
| | | |
sessions
| session_id | user_id | session_date | device_type |
|---|
| 101 | 1 | 2024-02-10 | iOS |
| 102 | 1 | 2024-02-20 | Web |
| 103 | 2 | 2024-02-15 | Android |
| 104 | 3 | 2024-01-25 | Web |
| 105 | 4 | 2024-03-01 | iOS |
| 106 | 5 | 2024-02-28 | Web |
| | | |
Expected Output
| acquisition_channel | cohort_users | retained_users | retention_rate_pct |
|---|
| Organic Search | 2 | 1 | 50.00 |
| Paid Search | 2 | 1 | 50.00 |
| Referral | 2 | 1 | 50.00 |
| Paid Social | 2 | 0 | 0.00 |
