Compare Retention by Acquisition Source | Dataford Interview Questions

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.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_source	VARCHAR(50)	Marketing or referral source that acquired the user

sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
user_id	INT	User associated with the session
session_date	DATE	Date the session occurred
device_type	VARCHAR(20)	Device used during the session

Your question is Compare Retention by Acquisition Source. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem