Task
NovaPlay wants to measure how well each acquisition channel retains new users after signup. Write a PostgreSQL query to calculate 30-day retention by acquisition channel.
A user is considered retained if they have at least one activity event on or after signup_date + 30 days and on or before signup_date + 60 days. Use each user's acquisition channel from the acquisitions table.
Requirements
- Return one row per acquisition channel.
- For each channel, calculate:
- total signed-up users
- retained users
- 30-day retention rate as a percentage rounded to 2 decimals
- Include users even if they have no matching acquisition row; label those as
'Unknown'.
- Order results by retention rate descending, then channel name ascending.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| signup_date | DATE | User signup date |
| country | VARCHAR(50) | User country |
acquisitions
| column | type | description |
|---|
| acquisition_id | INT | Unique acquisition record |
| user_id | INT | User identifier |
| channel | VARCHAR(50) | Acquisition channel |
| campaign_name | VARCHAR(100) | Campaign name |
activity_events
| column | type | description |
|---|
| event_id | INT | Unique event identifier |
| user_id | INT | User identifier |
| event_date | DATE | Activity date |
| event_type | VARCHAR(50) | Event type |
Sample Data
users
| user_id | signup_date | country |
|---|
| 4 | 2024-01-20 | CA |
| 1 | 2024-01-01 | US |
| 7 | 2024-02-10 | US |
| 2 | 2024-01-05 | US |
| 9 | 2024-02-20 | GB |
| 3 | 2024-01-10 | GB |
| 6 | 2024-02-01 | CA |
| 8 | 2024-02-15 | US |
| 5 | 2024-01-25 | US |
| 10 | 2024-03-01 | CA |
acquisitions
| acquisition_id | user_id | channel | campaign_name |
|---|
| 101 | 1 | Paid Search | Brand SEM |
| 102 | 2 | Organic Search | SEO Landing |
| 103 | 3 | Paid Social | Winter Prospecting |
| 104 | 4 | Referral | Invite Program |
| 105 | 5 | Paid Search | Generic SEM |
| 106 | 6 | Email | Reactivation Blast |
| 107 | 7 | Organic Search | Blog CTA |
| 108 | 8 | Paid Social | Video Ads |
| 109 | 9 | | Unattributed Import |
| 110 | 11 | Affiliate | Partner Network |
activity_events
| event_id | user_id | event_date | event_type |
|---|
| 1001 | 1 | 2024-01-15 | session |
| 1002 | 1 | 2024-02-05 | purchase |
| 1003 | 2 | 2024-02-04 | session |
| 1004 | 2 | 2024-02-10 | purchase |
| 1005 | 3 | 2024-02-08 | session |
| 1006 | 4 | 2024-02-19 | session |
| 1007 | 5 | 2024-03-01 | session |
| 1008 | 6 | 2024-03-05 | purchase |
| 1009 | 7 | 2024-03-12 | session |
| 1010 | 8 | 2024-03-20 | session |
| 1011 | 9 | 2024-03-25 | purchase |
| 1012 | 12 | 2024-03-10 | session |
Expected Output
| channel | total_users | retained_users | retention_rate_pct |
|---|
| Email | 1 | 1 | 100.00 |
| Organic Search | 2 | 2 | 100.00 |
| Unknown | 2 | 1 | 50.00 |
| Paid Search | 2 | 1 | 50.00 |
| Paid Social | 2 | 0 | 0.00 |
| Referral | 1 | 0 | 0.00 |
