Interview Guides

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

Compare User Retention by Cohort

Medium

SQL & Data Manipulation

Asked at 11 companies11Window FunctionsCTEsDate Functions

Also asked at

Problem

Task

StreamWave wants to compare monthly retention across signup cohorts. Write a PostgreSQL query that calculates retention by cohort month over time.

Requirements

Define each user's cohort as the month of their signup_date.
For each cohort, count how many distinct users were active in month 0, month 1, and month 2 based on activity_date.
Return the cohort size, retained users, and retention rate for each cohort month and month offset.
Exclude activity that occurred before a user's signup date, and only include month offsets from 0 to 2.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_channel	VARCHAR(50)	Marketing channel for signup

`user_activity`

column	type	description
activity_id	INT	Unique activity event identifier
user_id	INT	User tied to the activity event
activity_date	DATE	Date of the activity
activity_type	VARCHAR(50)	Type of product activity

Sample Data

`users`

user_id	signup_date	acquisition_channel
3	2024-02-20	Referral
1	2024-01-05	Paid Search
8	2024-03-18	Organic
5	2024-03-01	Paid Search

`user_activity`

activity_id	user_id	activity_date	activity_type
104	1	2024-02-10	stream
101	1	2024-01-06	login
111	3	2024-03-05	login
120	8	2024-04-02	stream

Expected Output

cohort_month	month_number	cohort_size	retained_users	retention_rate
2024-01	0	2	2	1.0000
2024-01	1	2	2	1.0000
2024-01	2	2	1	0.5000
2024-02	0	3	2	0.6667
2024-02	1	3	2	0.6667
2024-02	2	3	1	0.3333
2024-03	3	2	2	1.0000

Use distinct users for retention counts so multiple activity events in the same month do not inflate results.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_channel	VARCHAR(50)	Marketing acquisition source

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	User tied to the activity event
activity_date	DATE	Date of the activity event
activity_type	VARCHAR(50)	Type of user activity

Sample Data

Example 1

Input

{"users":[["3","2024-02-20","Referral"],["1","2024-01-05","Paid Search"],["8","2024-03-18","Organic"],["5","2024-03-01","Paid Search"],["2","2024-01-28","Organic"],["7","2024-02-28",null],["4","2024-02-02","Paid Social"],["6","2024-04-10","Referral"],["9","2024-01-15","Email"],["10","2024-02-10","Organic"],["11","2024-01-07","null"],["12","2024-02-12","Podcast"],["13","2024-02-28","Email"],["14","2024-01-14","Paid Social"],["15","2024-02-29","Email"],["16","2024-04-04","TikTok Ads"],["17","2024-

Output

[["2024-01","0","8","2","0.2500"],["2024-01","1","8","2","0.2500"],["2024-01","2","8","1","0.1250"],["2024-02","0","9","1","0.1111"],["2024-02","1","9","3","0.3333"],["2024-02","2","9","2","0.2222"],["2024-03","0","8","2","0.2500"],["2024-03","1","8","1","0.1250"],["2024-03","2","8","1","0.1250"],["2024-04","0","3","1","0.3333"]]

Problem

Task

StreamWave wants to compare monthly retention across signup cohorts. Write a PostgreSQL query that calculates retention by cohort month over time.

Requirements

Define each user's cohort as the month of their signup_date.
For each cohort, count how many distinct users were active in month 0, month 1, and month 2 based on activity_date.
Return the cohort size, retained users, and retention rate for each cohort month and month offset.
Exclude activity that occurred before a user's signup date, and only include month offsets from 0 to 2.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_channel	VARCHAR(50)	Marketing channel for signup

`user_activity`

column	type	description
activity_id	INT	Unique activity event identifier
user_id	INT	User tied to the activity event
activity_date	DATE	Date of the activity
activity_type	VARCHAR(50)	Type of product activity

Sample Data

`users`

user_id	signup_date	acquisition_channel
3	2024-02-20	Referral
1	2024-01-05	Paid Search
8	2024-03-18	Organic
5	2024-03-01	Paid Search

`user_activity`

activity_id	user_id	activity_date	activity_type
104	1	2024-02-10	stream
101	1	2024-01-06	login
111	3	2024-03-05	login
120	8	2024-04-02	stream

Expected Output

cohort_month	month_number	cohort_size	retained_users	retention_rate
2024-01	0	2	2	1.0000
2024-01	1	2	2	1.0000
2024-01	2	2	1	0.5000
2024-02	0	3	2	0.6667
2024-02	1	3	2	0.6667
2024-02	2	3	1	0.3333
2024-03	3	2	2	1.0000

Use distinct users for retention counts so multiple activity events in the same month do not inflate results.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_channel	VARCHAR(50)	Marketing acquisition source

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	User tied to the activity event
activity_date	DATE	Date of the activity event
activity_type	VARCHAR(50)	Type of user activity

Sample Data

Example 1

Input

{"users":[["3","2024-02-20","Referral"],["1","2024-01-05","Paid Search"],["8","2024-03-18","Organic"],["5","2024-03-01","Paid Search"],["2","2024-01-28","Organic"],["7","2024-02-28",null],["4","2024-02-02","Paid Social"],["6","2024-04-10","Referral"],["9","2024-01-15","Email"],["10","2024-02-10","Organic"],["11","2024-01-07","null"],["12","2024-02-12","Podcast"],["13","2024-02-28","Email"],["14","2024-01-14","Paid Social"],["15","2024-02-29","Email"],["16","2024-04-04","TikTok Ads"],["17","2024-

Output

[["2024-01","0","8","2","0.2500"],["2024-01","1","8","2","0.2500"],["2024-01","2","8","1","0.1250"],["2024-02","0","9","1","0.1111"],["2024-02","1","9","3","0.3333"],["2024-02","2","9","2","0.2222"],["2024-03","0","8","2","0.2500"],["2024-03","1","8","1","0.1250"],["2024-03","2","8","1","0.1250"],["2024-04","0","3","1","0.3333"]]

Practice SQL

PostgreSQL

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

Up next

Compare Signup Cohorts by RetentionMedium

Weekly Signup Cohort RetentionMedium

Monthly User Engagement Trend AnalysisMedium

Next question

Compare User Retention by Cohort

Medium

SQL & Data Manipulation

Asked at 11 companies11Window FunctionsCTEsDate Functions

Also asked at

Problem

Task

StreamWave wants to compare monthly retention across signup cohorts. Write a PostgreSQL query that calculates retention by cohort month over time.

Requirements

Define each user's cohort as the month of their signup_date.
For each cohort, count how many distinct users were active in month 0, month 1, and month 2 based on activity_date.
Return the cohort size, retained users, and retention rate for each cohort month and month offset.
Exclude activity that occurred before a user's signup date, and only include month offsets from 0 to 2.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_channel	VARCHAR(50)	Marketing channel for signup

`user_activity`

column	type	description
activity_id	INT	Unique activity event identifier
user_id	INT	User tied to the activity event
activity_date	DATE	Date of the activity
activity_type	VARCHAR(50)	Type of product activity

Sample Data

`users`

user_id	signup_date	acquisition_channel
3	2024-02-20	Referral
1	2024-01-05	Paid Search
8	2024-03-18	Organic
5	2024-03-01	Paid Search

`user_activity`

activity_id	user_id	activity_date	activity_type
104	1	2024-02-10	stream
101	1	2024-01-06	login
111	3	2024-03-05	login
120	8	2024-04-02	stream

Expected Output

cohort_month	month_number	cohort_size	retained_users	retention_rate
2024-01	0	2	2	1.0000
2024-01	1	2	2	1.0000
2024-01	2	2	1	0.5000
2024-02	0	3	2	0.6667
2024-02	1	3	2	0.6667
2024-02	2	3	1	0.3333
2024-03	3	2	2	1.0000

Use distinct users for retention counts so multiple activity events in the same month do not inflate results.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_channel	VARCHAR(50)	Marketing acquisition source

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	User tied to the activity event
activity_date	DATE	Date of the activity event
activity_type	VARCHAR(50)	Type of user activity

Sample Data

Example 1

Input

{"users":[["3","2024-02-20","Referral"],["1","2024-01-05","Paid Search"],["8","2024-03-18","Organic"],["5","2024-03-01","Paid Search"],["2","2024-01-28","Organic"],["7","2024-02-28",null],["4","2024-02-02","Paid Social"],["6","2024-04-10","Referral"],["9","2024-01-15","Email"],["10","2024-02-10","Organic"],["11","2024-01-07","null"],["12","2024-02-12","Podcast"],["13","2024-02-28","Email"],["14","2024-01-14","Paid Social"],["15","2024-02-29","Email"],["16","2024-04-04","TikTok Ads"],["17","2024-

Output

[["2024-01","0","8","2","0.2500"],["2024-01","1","8","2","0.2500"],["2024-01","2","8","1","0.1250"],["2024-02","0","9","1","0.1111"],["2024-02","1","9","3","0.3333"],["2024-02","2","9","2","0.2222"],["2024-03","0","8","2","0.2500"],["2024-03","1","8","1","0.1250"],["2024-03","2","8","1","0.1250"],["2024-04","0","3","1","0.3333"]]

Problem

Task

StreamWave wants to compare monthly retention across signup cohorts. Write a PostgreSQL query that calculates retention by cohort month over time.

Requirements

Define each user's cohort as the month of their signup_date.
For each cohort, count how many distinct users were active in month 0, month 1, and month 2 based on activity_date.
Return the cohort size, retained users, and retention rate for each cohort month and month offset.
Exclude activity that occurred before a user's signup date, and only include month offsets from 0 to 2.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_channel	VARCHAR(50)	Marketing channel for signup

`user_activity`

column	type	description
activity_id	INT	Unique activity event identifier
user_id	INT	User tied to the activity event
activity_date	DATE	Date of the activity
activity_type	VARCHAR(50)	Type of product activity

Sample Data

`users`

user_id	signup_date	acquisition_channel
3	2024-02-20	Referral
1	2024-01-05	Paid Search
8	2024-03-18	Organic
5	2024-03-01	Paid Search

`user_activity`

activity_id	user_id	activity_date	activity_type
104	1	2024-02-10	stream
101	1	2024-01-06	login
111	3	2024-03-05	login
120	8	2024-04-02	stream

Expected Output

cohort_month	month_number	cohort_size	retained_users	retention_rate
2024-01	0	2	2	1.0000
2024-01	1	2	2	1.0000
2024-01	2	2	1	0.5000
2024-02	0	3	2	0.6667
2024-02	1	3	2	0.6667
2024-02	2	3	1	0.3333
2024-03	3	2	2	1.0000

Use distinct users for retention counts so multiple activity events in the same month do not inflate results.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_channel	VARCHAR(50)	Marketing acquisition source

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	User tied to the activity event
activity_date	DATE	Date of the activity event
activity_type	VARCHAR(50)	Type of user activity

Sample Data

Example 1

Input

{"users":[["3","2024-02-20","Referral"],["1","2024-01-05","Paid Search"],["8","2024-03-18","Organic"],["5","2024-03-01","Paid Search"],["2","2024-01-28","Organic"],["7","2024-02-28",null],["4","2024-02-02","Paid Social"],["6","2024-04-10","Referral"],["9","2024-01-15","Email"],["10","2024-02-10","Organic"],["11","2024-01-07","null"],["12","2024-02-12","Podcast"],["13","2024-02-28","Email"],["14","2024-01-14","Paid Social"],["15","2024-02-29","Email"],["16","2024-04-04","TikTok Ads"],["17","2024-

Output

[["2024-01","0","8","2","0.2500"],["2024-01","1","8","2","0.2500"],["2024-01","2","8","1","0.1250"],["2024-02","0","9","1","0.1111"],["2024-02","1","9","3","0.3333"],["2024-02","2","9","2","0.2222"],["2024-03","0","8","2","0.2500"],["2024-03","1","8","1","0.1250"],["2024-03","2","8","1","0.1250"],["2024-04","0","3","1","0.3333"]]

Practice SQL

PostgreSQL

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

Up next

Compare Signup Cohorts by RetentionMedium

Weekly Signup Cohort RetentionMedium

Monthly User Engagement Trend AnalysisMedium

Next question