Interview Guides

Compare Signup Cohorts by Retention | Dataford Interview Questions - Dataford - Ace your Interview

Compare Signup Cohorts by Retention

Medium

SQL & Data Manipulation

Asked at 3 companies3Window FunctionsCTEsDate Functions

Also asked at

Problem

Task

StreamWave wants to compare how different signup cohorts behave after registration. Write a SQL query to measure 30-day engagement by monthly signup cohort.

Requirements

Define each user's cohort as the month of their signup_date.
For each cohort month, calculate:
- total users in the cohort
- users with at least one session_start event within 30 days of signup
- the percentage of engaged users in the cohort
Return one row per cohort month.
Order results by cohort month ascending.

Table Definitions

`users`

Column	Type	Description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user registered
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user

`events`

Column	Type	Description
event_id	INT	Unique event identifier
user_id	INT	User who generated the event
event_type	VARCHAR(50)	Event name
event_date	DATE	Date of the event

Sample Data

`users`

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

`events`

event_id	user_id	event_type	event_date
104	2	purchase	2024-02-10
101	1	session_start	2024-01-06
110	5	session_start	2024-03-30
116	10	session_start	2024-03-10

Expected Output

cohort_month	total_users	engaged_users_30d	engagement_rate_30d
2024-01	4	3	75.00
2024-02	3	2	66.67
2024-03	3	1	33.33

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 that acquired the user

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who generated the event
event_type	VARCHAR(50)	Type of user event
event_date	DATE	Date the event occurred

Sample Data

Example 1

Input

{"users":[["3","2024-02-20","Organic"],["1","2024-01-05","Paid Search"],["8","2024-03-28","Referral"],["5","2024-02-25","Email"],["2","2024-01-15","Organic"],["10","2024-03-01",null],["4","2024-02-01","Paid Social"],["7","2024-03-10","Paid Search"],["6","2024-01-31","Referral"],["9","2024-01-20",""],["11","2024-03-22","Referral"],["12","2024-03-31","Content"],["13","2024-01-20","Referral"],["14","2024-01-11","Influencer"],["15","2024-02-09","Organic"],["16","2024-02-07","Instagram"],["17","2024-

Output[["2024-01","10","3","30.00"],["2024-02","9","2","22.22"],["2024-03","9","1","11.11"]]

Problem

Task

StreamWave wants to compare how different signup cohorts behave after registration. Write a SQL query to measure 30-day engagement by monthly signup cohort.

Requirements

Define each user's cohort as the month of their signup_date.
For each cohort month, calculate:
- total users in the cohort
- users with at least one session_start event within 30 days of signup
- the percentage of engaged users in the cohort
Return one row per cohort month.
Order results by cohort month ascending.

Table Definitions

`users`

Column	Type	Description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user registered
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user

`events`

Column	Type	Description
event_id	INT	Unique event identifier
user_id	INT	User who generated the event
event_type	VARCHAR(50)	Event name
event_date	DATE	Date of the event

Sample Data

`users`

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

`events`

event_id	user_id	event_type	event_date
104	2	purchase	2024-02-10
101	1	session_start	2024-01-06
110	5	session_start	2024-03-30
116	10	session_start	2024-03-10

Expected Output

cohort_month	total_users	engaged_users_30d	engagement_rate_30d
2024-01	4	3	75.00
2024-02	3	2	66.67
2024-03	3	1	33.33

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 that acquired the user

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who generated the event
event_type	VARCHAR(50)	Type of user event
event_date	DATE	Date the event occurred

Sample Data

Example 1

Input

{"users":[["3","2024-02-20","Organic"],["1","2024-01-05","Paid Search"],["8","2024-03-28","Referral"],["5","2024-02-25","Email"],["2","2024-01-15","Organic"],["10","2024-03-01",null],["4","2024-02-01","Paid Social"],["7","2024-03-10","Paid Search"],["6","2024-01-31","Referral"],["9","2024-01-20",""],["11","2024-03-22","Referral"],["12","2024-03-31","Content"],["13","2024-01-20","Referral"],["14","2024-01-11","Influencer"],["15","2024-02-09","Organic"],["16","2024-02-07","Instagram"],["17","2024-

Output[["2024-01","10","3","30.00"],["2024-02","9","2","22.22"],["2024-03","9","1","11.11"]]

Practice SQL

PostgreSQL

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

Up next

Compare User Retention by CohortMedium

Weekly Signup Cohort RetentionMedium

Monthly User Engagement Trend AnalysisMedium

Next question

Compare Signup Cohorts by Retention

Medium

SQL & Data Manipulation

Asked at 3 companies3Window FunctionsCTEsDate Functions

Also asked at

Problem

Task

StreamWave wants to compare how different signup cohorts behave after registration. Write a SQL query to measure 30-day engagement by monthly signup cohort.

Requirements

Define each user's cohort as the month of their signup_date.
For each cohort month, calculate:
- total users in the cohort
- users with at least one session_start event within 30 days of signup
- the percentage of engaged users in the cohort
Return one row per cohort month.
Order results by cohort month ascending.

Table Definitions

`users`

Column	Type	Description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user registered
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user

`events`

Column	Type	Description
event_id	INT	Unique event identifier
user_id	INT	User who generated the event
event_type	VARCHAR(50)	Event name
event_date	DATE	Date of the event

Sample Data

`users`

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

`events`

event_id	user_id	event_type	event_date
104	2	purchase	2024-02-10
101	1	session_start	2024-01-06
110	5	session_start	2024-03-30
116	10	session_start	2024-03-10

Expected Output

cohort_month	total_users	engaged_users_30d	engagement_rate_30d
2024-01	4	3	75.00
2024-02	3	2	66.67
2024-03	3	1	33.33

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 that acquired the user

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who generated the event
event_type	VARCHAR(50)	Type of user event
event_date	DATE	Date the event occurred

Sample Data

Example 1

Input

{"users":[["3","2024-02-20","Organic"],["1","2024-01-05","Paid Search"],["8","2024-03-28","Referral"],["5","2024-02-25","Email"],["2","2024-01-15","Organic"],["10","2024-03-01",null],["4","2024-02-01","Paid Social"],["7","2024-03-10","Paid Search"],["6","2024-01-31","Referral"],["9","2024-01-20",""],["11","2024-03-22","Referral"],["12","2024-03-31","Content"],["13","2024-01-20","Referral"],["14","2024-01-11","Influencer"],["15","2024-02-09","Organic"],["16","2024-02-07","Instagram"],["17","2024-

Output[["2024-01","10","3","30.00"],["2024-02","9","2","22.22"],["2024-03","9","1","11.11"]]

Problem

Task

StreamWave wants to compare how different signup cohorts behave after registration. Write a SQL query to measure 30-day engagement by monthly signup cohort.

Requirements

Define each user's cohort as the month of their signup_date.
For each cohort month, calculate:
- total users in the cohort
- users with at least one session_start event within 30 days of signup
- the percentage of engaged users in the cohort
Return one row per cohort month.
Order results by cohort month ascending.

Table Definitions

`users`

Column	Type	Description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user registered
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user

`events`

Column	Type	Description
event_id	INT	Unique event identifier
user_id	INT	User who generated the event
event_type	VARCHAR(50)	Event name
event_date	DATE	Date of the event

Sample Data

`users`

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

`events`

event_id	user_id	event_type	event_date
104	2	purchase	2024-02-10
101	1	session_start	2024-01-06
110	5	session_start	2024-03-30
116	10	session_start	2024-03-10

Expected Output

cohort_month	total_users	engaged_users_30d	engagement_rate_30d
2024-01	4	3	75.00
2024-02	3	2	66.67
2024-03	3	1	33.33

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 that acquired the user

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who generated the event
event_type	VARCHAR(50)	Type of user event
event_date	DATE	Date the event occurred

Sample Data

Example 1

Input

{"users":[["3","2024-02-20","Organic"],["1","2024-01-05","Paid Search"],["8","2024-03-28","Referral"],["5","2024-02-25","Email"],["2","2024-01-15","Organic"],["10","2024-03-01",null],["4","2024-02-01","Paid Social"],["7","2024-03-10","Paid Search"],["6","2024-01-31","Referral"],["9","2024-01-20",""],["11","2024-03-22","Referral"],["12","2024-03-31","Content"],["13","2024-01-20","Referral"],["14","2024-01-11","Influencer"],["15","2024-02-09","Organic"],["16","2024-02-07","Instagram"],["17","2024-

Output[["2024-01","10","3","30.00"],["2024-02","9","2","22.22"],["2024-03","9","1","11.11"]]

Practice SQL

PostgreSQL

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

Up next

Compare User Retention by CohortMedium

Weekly Signup Cohort RetentionMedium

Monthly User Engagement Trend AnalysisMedium

Next question