30-Day Retention by Channel | Dataford Interview Questions

Your question is 30-Day Retention by Channel. Start with the requirements and the three tables on the right.

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

Problem

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.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
country	VARCHAR(50)	User country

acquisitions

Column	Type	Description
acquisition_idPK	INT	Unique acquisition record identifier
user_id	INT	User tied to the acquisition record
channel	VARCHAR(50)	Acquisition channel
campaign_name	VARCHAR(100)	Marketing campaign name

activity_events

Column	Type	Description
event_idPK	INT	Unique activity event identifier
user_id	INT	User who generated the event
event_date	DATE	Date of the activity event
event_type	VARCHAR(50)	Type of user activity