Task
NovaPlay wants to compare user retention across acquisition sources. Write a PostgreSQL query to calculate month-1 retention by acquisition source.
A user is considered retained in month 1 if they have at least one session in the calendar month immediately after their signup month.
Requirements
- For each acquisition source, count total signed-up users.
- Count how many of those users were retained in month 1.
- Calculate retention rate as
retained_users / total_users, rounded to 4 decimal places.
- Include users with
NULL acquisition sources and label them as 'Unknown'.
- Return results ordered by retention rate descending, then acquisition source ascending.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| signup_date | DATE | Date the user signed up |
| acquisition_source | VARCHAR(50) | Signup source such as Paid Search or Organic |
sessions
| column | type | description |
|---|
| session_id | INT | Unique session identifier |
| user_id | INT | User tied to 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_source |
|---|
| 4 | 2024-02-20 | Referral |
| 1 | 2024-01-15 | Paid Search |
| 7 | 2024-03-15 | NULL |
| 2 | 2024-01-28 | Organic |
sessions
| session_id | user_id | session_date | device_type |
|---|
| 105 | 1 | 2024-02-10 | Web |
| 101 | 1 | 2024-01-20 | iOS |
| 110 | 4 | 2024-03-01 | Android |
| 116 | 12 | 2024-03-10 | Web |
Expected Output
| acquisition_source | total_users | retained_users | retention_rate |
|---|
| Paid Search | 3 | 2 | 0.6667 |
| Referral | 3 | 2 | 0.6667 |
| Organic | 3 | 1 | 0.3333 |
| Unknown | 2 | 0 | 0.0000 |
