Compare Channel Retention by Cohort | Dataford Interview Questions

Your question is Compare Channel Retention by Cohort. Start with the requirements and the two 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

BrightCart wants to compare user retention across acquisition channels. Write a SQL query to calculate day-30 retention by acquisition channel for users who signed up in January 2024.

A user is considered retained if they have at least one session on or after 30 days from signup and before 60 days from signup.

Requirements

Use each user's acquisition channel from the users table.
Restrict the cohort to users whose signup_date is between 2024-01-01 and 2024-01-31.
For each channel, return:
- total users in the cohort
- retained users
- retention rate as a percentage rounded to 2 decimals
Include channels with zero retained users.
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
acquisition_channel	VARCHAR(50)	Marketing channel credited for acquisition
country_code	VARCHAR(2)	Two-letter country code

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 for the session