Top Channels by Retention | Dataford Interview Questions

Write a SQL query to find the top three performing marketing channels based on user retention over a 6-month period.

Assume users acquired in January 2025 are evaluated for activity during June 2025. A retained user is an eligible user with at least one activity during June.

Output

One row per marketing channel with eligible users.
Columns: channel_id, channel_name, eligible_users, retained_users, and retention_rate.
Include the three highest retention rates, ordered by retention_rate descending, then channel_name ascending to break ties.

Schema

marketing_channels

Column	Type	Description
channel_idPK	INT	Unique marketing channel identifier
channel_name	VARCHAR(100)	Marketing channel name

users

Column	Type	Description
user_idPK	INT	Unique user identifier
channel_id	INT	Acquisition channel identifier
signup_date	DATE	Date the user signed up

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity record identifier
user_id	INT	User who performed the activity
activity_date	DATE	Date of user activity
activity_type	VARCHAR(50)	Type of user activity

Your question is Top Channels by Retention. Start with the requirements and the three 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