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 |
