Interview Guides

Compare Channel Retention by Cohort | Dataford Interview Questions - Dataford - Ace your Interview

Compare Channel Retention by Cohort

Medium

SQL & Data Manipulation

Asked at 9 companies9JoinsAggregationsGroup ByDate Functions

Also asked at

Problem

Task

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

Sample Data

`users`

user_id	signup_date	acquisition_channel	country_code
1	2024-01-05	Paid Search	US
2	2024-01-10	Organic Search	CA
3	2024-01-15	Paid Social	US
4	2024-01-20	Referral	GB
5	2024-01-25	Paid Search	US
6	2024-02-01	Organic Search	US

`sessions`

session_id	user_id	session_date	device_type
101	1	2024-02-10	iOS
102	1	2024-02-20	Web
103	2	2024-02-15	Android
104	3	2024-01-25	Web
105	4	2024-03-01	iOS
106	5	2024-02-28	Web

Expected Output

acquisition_channel	cohort_users	retained_users	retention_rate_pct
Organic Search	2	1	50.00
Paid Search	2	1	50.00
Referral	2	1	50.00
Paid Social	2	0	0.00

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

Sample Data

Example 1

Input

{"users":[["1","2024-01-05","Paid Search","US"],["2","2024-01-08","Organic Search","CA"],["3","2024-01-12","Paid Social","US"],["4","2024-01-18","Referral","GB"],["5","2024-01-22","Paid Search","US"],["6","2024-01-28","Organic Search",null],["7","2024-01-30","Referral","DE"],["8","2024-02-02","Paid Search","US"],["9","2024-01-14","Paid Social","FR"],["10","2023-12-29",null,"US"],["11","2023-12-26","LinkedIn Ads","IE"],["12","2024-01-21","Referral","PL"],["13","2024-01-03","null","AE"],["14","202

Output

[["Paid Search","2","2","100.00"],["Paid Social","2","1","50.00"],["Organic Search","3","1","33.33"],["Referral","3","1","33.33"],["Email","1","0","0.00"],["Event","2","0","0.00"],["Influencer","1","0","0.00"],["PR","1","0","0.00"],["Webinar","1","0","0.00"],["null","1","0","0.00"]]

Problem

Task

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

Sample Data

`users`

user_id	signup_date	acquisition_channel	country_code
1	2024-01-05	Paid Search	US
2	2024-01-10	Organic Search	CA
3	2024-01-15	Paid Social	US
4	2024-01-20	Referral	GB
5	2024-01-25	Paid Search	US
6	2024-02-01	Organic Search	US

`sessions`

session_id	user_id	session_date	device_type
101	1	2024-02-10	iOS
102	1	2024-02-20	Web
103	2	2024-02-15	Android
104	3	2024-01-25	Web
105	4	2024-03-01	iOS
106	5	2024-02-28	Web

Expected Output

acquisition_channel	cohort_users	retained_users	retention_rate_pct
Organic Search	2	1	50.00
Paid Search	2	1	50.00
Referral	2	1	50.00
Paid Social	2	0	0.00

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

Sample Data

Example 1

Input

{"users":[["1","2024-01-05","Paid Search","US"],["2","2024-01-08","Organic Search","CA"],["3","2024-01-12","Paid Social","US"],["4","2024-01-18","Referral","GB"],["5","2024-01-22","Paid Search","US"],["6","2024-01-28","Organic Search",null],["7","2024-01-30","Referral","DE"],["8","2024-02-02","Paid Search","US"],["9","2024-01-14","Paid Social","FR"],["10","2023-12-29",null,"US"],["11","2023-12-26","LinkedIn Ads","IE"],["12","2024-01-21","Referral","PL"],["13","2024-01-03","null","AE"],["14","202

Output

[["Paid Search","2","2","100.00"],["Paid Social","2","1","50.00"],["Organic Search","3","1","33.33"],["Referral","3","1","33.33"],["Email","1","0","0.00"],["Event","2","0","0.00"],["Influencer","1","0","0.00"],["PR","1","0","0.00"],["Webinar","1","0","0.00"],["null","1","0","0.00"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

30-Day Retention by ChannelMedium

Compare Signup Cohorts by RetentionMedium

Calculate 30-Day User RetentionMedium

Next question

Compare Channel Retention by Cohort

Medium

SQL & Data Manipulation

Asked at 9 companies9JoinsAggregationsGroup ByDate Functions

Also asked at

Problem

Task

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

Sample Data

`users`

user_id	signup_date	acquisition_channel	country_code
1	2024-01-05	Paid Search	US
2	2024-01-10	Organic Search	CA
3	2024-01-15	Paid Social	US
4	2024-01-20	Referral	GB
5	2024-01-25	Paid Search	US
6	2024-02-01	Organic Search	US

`sessions`

session_id	user_id	session_date	device_type
101	1	2024-02-10	iOS
102	1	2024-02-20	Web
103	2	2024-02-15	Android
104	3	2024-01-25	Web
105	4	2024-03-01	iOS
106	5	2024-02-28	Web

Expected Output

acquisition_channel	cohort_users	retained_users	retention_rate_pct
Organic Search	2	1	50.00
Paid Search	2	1	50.00
Referral	2	1	50.00
Paid Social	2	0	0.00

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

Sample Data

Example 1

Input

{"users":[["1","2024-01-05","Paid Search","US"],["2","2024-01-08","Organic Search","CA"],["3","2024-01-12","Paid Social","US"],["4","2024-01-18","Referral","GB"],["5","2024-01-22","Paid Search","US"],["6","2024-01-28","Organic Search",null],["7","2024-01-30","Referral","DE"],["8","2024-02-02","Paid Search","US"],["9","2024-01-14","Paid Social","FR"],["10","2023-12-29",null,"US"],["11","2023-12-26","LinkedIn Ads","IE"],["12","2024-01-21","Referral","PL"],["13","2024-01-03","null","AE"],["14","202

Output

[["Paid Search","2","2","100.00"],["Paid Social","2","1","50.00"],["Organic Search","3","1","33.33"],["Referral","3","1","33.33"],["Email","1","0","0.00"],["Event","2","0","0.00"],["Influencer","1","0","0.00"],["PR","1","0","0.00"],["Webinar","1","0","0.00"],["null","1","0","0.00"]]

Problem

Task

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

Sample Data

`users`

user_id	signup_date	acquisition_channel	country_code
1	2024-01-05	Paid Search	US
2	2024-01-10	Organic Search	CA
3	2024-01-15	Paid Social	US
4	2024-01-20	Referral	GB
5	2024-01-25	Paid Search	US
6	2024-02-01	Organic Search	US

`sessions`

session_id	user_id	session_date	device_type
101	1	2024-02-10	iOS
102	1	2024-02-20	Web
103	2	2024-02-15	Android
104	3	2024-01-25	Web
105	4	2024-03-01	iOS
106	5	2024-02-28	Web

Expected Output

acquisition_channel	cohort_users	retained_users	retention_rate_pct
Organic Search	2	1	50.00
Paid Search	2	1	50.00
Referral	2	1	50.00
Paid Social	2	0	0.00

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

Sample Data

Example 1

Input

{"users":[["1","2024-01-05","Paid Search","US"],["2","2024-01-08","Organic Search","CA"],["3","2024-01-12","Paid Social","US"],["4","2024-01-18","Referral","GB"],["5","2024-01-22","Paid Search","US"],["6","2024-01-28","Organic Search",null],["7","2024-01-30","Referral","DE"],["8","2024-02-02","Paid Search","US"],["9","2024-01-14","Paid Social","FR"],["10","2023-12-29",null,"US"],["11","2023-12-26","LinkedIn Ads","IE"],["12","2024-01-21","Referral","PL"],["13","2024-01-03","null","AE"],["14","202

Output

[["Paid Search","2","2","100.00"],["Paid Social","2","1","50.00"],["Organic Search","3","1","33.33"],["Referral","3","1","33.33"],["Email","1","0","0.00"],["Event","2","0","0.00"],["Influencer","1","0","0.00"],["PR","1","0","0.00"],["Webinar","1","0","0.00"],["null","1","0","0.00"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

30-Day Retention by ChannelMedium

Compare Signup Cohorts by RetentionMedium

Calculate 30-Day User RetentionMedium

Next question