Compare Channel Retention by Cohort | Dataford Interview Questions

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.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
signup_date	DATE	Date the user created an account
acquisition_channel	VARCHAR(50)	Marketing channel attributed at signup
country_code	VARCHAR(2)	User country

`sessions`

column	type	description
session_id	INT	Primary key for each session
user_id	INT	User who started the session
session_date	DATE	Date of the session
device_type	VARCHAR(20)	Device used for the session

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.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
signup_date	DATE	Date the user created an account
acquisition_channel	VARCHAR(50)	Marketing channel attributed at signup
country_code	VARCHAR(2)	User country

`sessions`

column	type	description
session_id	INT	Primary key for each session
user_id	INT	User who started the session
session_date	DATE	Date of the session
device_type	VARCHAR(20)	Device used for the session

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.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
signup_date	DATE	Date the user created an account
acquisition_channel	VARCHAR(50)	Marketing channel attributed at signup
country_code	VARCHAR(2)	User country

`sessions`

column	type	description
session_id	INT	Primary key for each session
user_id	INT	User who started the session
session_date	DATE	Date of the session
device_type	VARCHAR(20)	Device used for the session

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.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
signup_date	DATE	Date the user created an account
acquisition_channel	VARCHAR(50)	Marketing channel attributed at signup
country_code	VARCHAR(2)	User country

`sessions`

column	type	description
session_id	INT	Primary key for each session
user_id	INT	User who started the session
session_date	DATE	Date of the session
device_type	VARCHAR(20)	Device used for the session