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 |
