Interview Guides

Measure Retention After Product Launch | Dataford Interview Questions - Dataford - Ace your Interview

Measure Retention After Product Launch

Medium

SQL & Data Manipulation

Asked at 1 company1AggregationsWindow FunctionsDate Functions

Also asked at

Problem

Task

NovaPlay launched a major product update on 2024-04-01 and wants to understand whether user retention changed after the launch. Write a PostgreSQL query that uses a cohort table to compare retention for pre-launch and post-launch signup cohorts.

Requirements

Treat each user's cohort as the month of their signup_date from the cohort_table.
For each cohort month, calculate month 1 retention: the percentage of users who had at least one activity event in the calendar month immediately after their cohort month.
Label each cohort as pre_launch or post_launch based on whether the cohort month is before 2024-04-01.
Return one row per cohort month with cohort size, retained users, and retention rate rounded to 2 decimals.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	User signup date
country	VARCHAR(50)	User country

`cohort_table`

column	type	description
user_id	INT	User identifier
cohort_month	DATE	First day of the user's signup month

`user_activity`

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

Sample Data

`users`

user_id	signup_date	country
5	2024-04-20	US
1	2024-02-10	US
8	2024-05-18	BR

`cohort_table`

user_id	cohort_month
1	2024-02-01
5	2024-04-01
8	2024-05-01

`user_activity`

activity_id	user_id	activity_date	activity_type
104	1	2024-03-05	session
111	5	2024-05-05	purchase
116	8	2024-06-02	session

Expected Output

cohort_month	launch_period	cohort_size	retained_users	retention_rate
2024-02-01	pre_launch	2	1	50.00
2024-03-01	pre_launch	2	1	50.00
2024-04-01	post_launch	3	2	66.67
2024-05-01	post_launch	2	1	50.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
country	VARCHAR(50)	User country

cohort_table

Column	Type	Description
user_idPK	INT	User identifier
cohort_month	DATE	First day of the user's signup month

user_activity

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

Sample Data

Example 1

Input

{"users":[[5,"2024-04-20","US"],[1,"2024-02-10","US"],[7,"2024-05-03","CA"],[3,"2024-03-12","GB"],[10,"2024-06-01","US"],[2,"2024-02-25","CA"],[9,"2024-04-30",null],[4,"2024-03-28","US"],[8,"2024-05-18","BR"],[6,"2024-04-02","GB"],["1","2024-02-26","US"],["2","2024-03-04","AU"],["3","2024-02-24","AR"],["4","2024-05-12","null"],["5","2024-02-07","NO"],["6","2024-02-24","IN"],["7","2024-04-03","SE"],["8","2024-03-14","DK"],["9","2024-05-29","MX"],["10","2024-03-10","NO"],["11","2024-04-08","DK"],[

Output

[["2024-02-01","pre_launch","2","2","100.00"],["2024-02-04","pre_launch","1","0","0.00"],["2024-02-07","pre_launch","2","0","0.00"],["2024-02-19","pre_launch","1","0","0.00"],["2024-02-25","pre_launch","1","0","0.00"],["2024-02-26","pre_launch","2","0","0.00"],["2024-02-28","pre_launch","1","0","0.00"],["2024-03-01","pre_launch","2","2","100.00"],["2024-04-01","post_launch","3","2","66.67"],["2024-04-22","post_launch","1","0","0.00"],["2024-05-01","post_launch","2","2","100.00"],["2024-06-01","p

Problem

Task

Requirements

Treat each user's cohort as the month of their signup_date from the cohort_table.
For each cohort month, calculate month 1 retention: the percentage of users who had at least one activity event in the calendar month immediately after their cohort month.
Label each cohort as pre_launch or post_launch based on whether the cohort month is before 2024-04-01.
Return one row per cohort month with cohort size, retained users, and retention rate rounded to 2 decimals.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	User signup date
country	VARCHAR(50)	User country

`cohort_table`

column	type	description
user_id	INT	User identifier
cohort_month	DATE	First day of the user's signup month

`user_activity`

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

Sample Data

`users`

user_id	signup_date	country
5	2024-04-20	US
1	2024-02-10	US
8	2024-05-18	BR

`cohort_table`

user_id	cohort_month
1	2024-02-01
5	2024-04-01
8	2024-05-01

`user_activity`

activity_id	user_id	activity_date	activity_type
104	1	2024-03-05	session
111	5	2024-05-05	purchase
116	8	2024-06-02	session

Expected Output

cohort_month	launch_period	cohort_size	retained_users	retention_rate
2024-02-01	pre_launch	2	1	50.00
2024-03-01	pre_launch	2	1	50.00
2024-04-01	post_launch	3	2	66.67
2024-05-01	post_launch	2	1	50.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
country	VARCHAR(50)	User country

cohort_table

Column	Type	Description
user_idPK	INT	User identifier
cohort_month	DATE	First day of the user's signup month

user_activity

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

Sample Data

Example 1

Input

{"users":[[5,"2024-04-20","US"],[1,"2024-02-10","US"],[7,"2024-05-03","CA"],[3,"2024-03-12","GB"],[10,"2024-06-01","US"],[2,"2024-02-25","CA"],[9,"2024-04-30",null],[4,"2024-03-28","US"],[8,"2024-05-18","BR"],[6,"2024-04-02","GB"],["1","2024-02-26","US"],["2","2024-03-04","AU"],["3","2024-02-24","AR"],["4","2024-05-12","null"],["5","2024-02-07","NO"],["6","2024-02-24","IN"],["7","2024-04-03","SE"],["8","2024-03-14","DK"],["9","2024-05-29","MX"],["10","2024-03-10","NO"],["11","2024-04-08","DK"],[

Output

[["2024-02-01","pre_launch","2","2","100.00"],["2024-02-04","pre_launch","1","0","0.00"],["2024-02-07","pre_launch","2","0","0.00"],["2024-02-19","pre_launch","1","0","0.00"],["2024-02-25","pre_launch","1","0","0.00"],["2024-02-26","pre_launch","2","0","0.00"],["2024-02-28","pre_launch","1","0","0.00"],["2024-03-01","pre_launch","2","2","100.00"],["2024-04-01","post_launch","3","2","66.67"],["2024-04-22","post_launch","1","0","0.00"],["2024-05-01","post_launch","2","2","100.00"],["2024-06-01","p

Practice SQL

PostgreSQL

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

Up next

Compare Retention Before Product ChangeMedium

Compare Retention by Acquisition SourceMedium

Detect Post-Launch Customer Behavior ShiftsMedium

Next question

Measure Retention After Product Launch

Medium

SQL & Data Manipulation

Asked at 1 company1AggregationsWindow FunctionsDate Functions

Also asked at

Problem

Task

Requirements

Treat each user's cohort as the month of their signup_date from the cohort_table.
For each cohort month, calculate month 1 retention: the percentage of users who had at least one activity event in the calendar month immediately after their cohort month.
Label each cohort as pre_launch or post_launch based on whether the cohort month is before 2024-04-01.
Return one row per cohort month with cohort size, retained users, and retention rate rounded to 2 decimals.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	User signup date
country	VARCHAR(50)	User country

`cohort_table`

column	type	description
user_id	INT	User identifier
cohort_month	DATE	First day of the user's signup month

`user_activity`

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

Sample Data

`users`

user_id	signup_date	country
5	2024-04-20	US
1	2024-02-10	US
8	2024-05-18	BR

`cohort_table`

user_id	cohort_month
1	2024-02-01
5	2024-04-01
8	2024-05-01

`user_activity`

activity_id	user_id	activity_date	activity_type
104	1	2024-03-05	session
111	5	2024-05-05	purchase
116	8	2024-06-02	session

Expected Output

cohort_month	launch_period	cohort_size	retained_users	retention_rate
2024-02-01	pre_launch	2	1	50.00
2024-03-01	pre_launch	2	1	50.00
2024-04-01	post_launch	3	2	66.67
2024-05-01	post_launch	2	1	50.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
country	VARCHAR(50)	User country

cohort_table

Column	Type	Description
user_idPK	INT	User identifier
cohort_month	DATE	First day of the user's signup month

user_activity

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

Sample Data

Example 1

Input

{"users":[[5,"2024-04-20","US"],[1,"2024-02-10","US"],[7,"2024-05-03","CA"],[3,"2024-03-12","GB"],[10,"2024-06-01","US"],[2,"2024-02-25","CA"],[9,"2024-04-30",null],[4,"2024-03-28","US"],[8,"2024-05-18","BR"],[6,"2024-04-02","GB"],["1","2024-02-26","US"],["2","2024-03-04","AU"],["3","2024-02-24","AR"],["4","2024-05-12","null"],["5","2024-02-07","NO"],["6","2024-02-24","IN"],["7","2024-04-03","SE"],["8","2024-03-14","DK"],["9","2024-05-29","MX"],["10","2024-03-10","NO"],["11","2024-04-08","DK"],[

Output

[["2024-02-01","pre_launch","2","2","100.00"],["2024-02-04","pre_launch","1","0","0.00"],["2024-02-07","pre_launch","2","0","0.00"],["2024-02-19","pre_launch","1","0","0.00"],["2024-02-25","pre_launch","1","0","0.00"],["2024-02-26","pre_launch","2","0","0.00"],["2024-02-28","pre_launch","1","0","0.00"],["2024-03-01","pre_launch","2","2","100.00"],["2024-04-01","post_launch","3","2","66.67"],["2024-04-22","post_launch","1","0","0.00"],["2024-05-01","post_launch","2","2","100.00"],["2024-06-01","p

Problem

Task

Requirements

Treat each user's cohort as the month of their signup_date from the cohort_table.
For each cohort month, calculate month 1 retention: the percentage of users who had at least one activity event in the calendar month immediately after their cohort month.
Label each cohort as pre_launch or post_launch based on whether the cohort month is before 2024-04-01.
Return one row per cohort month with cohort size, retained users, and retention rate rounded to 2 decimals.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	User signup date
country	VARCHAR(50)	User country

`cohort_table`

column	type	description
user_id	INT	User identifier
cohort_month	DATE	First day of the user's signup month

`user_activity`

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

Sample Data

`users`

user_id	signup_date	country
5	2024-04-20	US
1	2024-02-10	US
8	2024-05-18	BR

`cohort_table`

user_id	cohort_month
1	2024-02-01
5	2024-04-01
8	2024-05-01

`user_activity`

activity_id	user_id	activity_date	activity_type
104	1	2024-03-05	session
111	5	2024-05-05	purchase
116	8	2024-06-02	session

Expected Output

cohort_month	launch_period	cohort_size	retained_users	retention_rate
2024-02-01	pre_launch	2	1	50.00
2024-03-01	pre_launch	2	1	50.00
2024-04-01	post_launch	3	2	66.67
2024-05-01	post_launch	2	1	50.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
country	VARCHAR(50)	User country

cohort_table

Column	Type	Description
user_idPK	INT	User identifier
cohort_month	DATE	First day of the user's signup month

user_activity

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

Sample Data

Example 1

Input

{"users":[[5,"2024-04-20","US"],[1,"2024-02-10","US"],[7,"2024-05-03","CA"],[3,"2024-03-12","GB"],[10,"2024-06-01","US"],[2,"2024-02-25","CA"],[9,"2024-04-30",null],[4,"2024-03-28","US"],[8,"2024-05-18","BR"],[6,"2024-04-02","GB"],["1","2024-02-26","US"],["2","2024-03-04","AU"],["3","2024-02-24","AR"],["4","2024-05-12","null"],["5","2024-02-07","NO"],["6","2024-02-24","IN"],["7","2024-04-03","SE"],["8","2024-03-14","DK"],["9","2024-05-29","MX"],["10","2024-03-10","NO"],["11","2024-04-08","DK"],[

Output

[["2024-02-01","pre_launch","2","2","100.00"],["2024-02-04","pre_launch","1","0","0.00"],["2024-02-07","pre_launch","2","0","0.00"],["2024-02-19","pre_launch","1","0","0.00"],["2024-02-25","pre_launch","1","0","0.00"],["2024-02-26","pre_launch","2","0","0.00"],["2024-02-28","pre_launch","1","0","0.00"],["2024-03-01","pre_launch","2","2","100.00"],["2024-04-01","post_launch","3","2","66.67"],["2024-04-22","post_launch","1","0","0.00"],["2024-05-01","post_launch","2","2","100.00"],["2024-06-01","p

Practice SQL

PostgreSQL

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

Up next

Compare Retention Before Product ChangeMedium

Compare Retention by Acquisition SourceMedium

Detect Post-Launch Customer Behavior ShiftsMedium

Next question