Weekly Signup Cohort Retention | Dataford Interview Questions

StreamWave wants to measure early user retention by signup cohort. Write a SQL query to calculate week-over-week retention for users who signed up in the last 90 days.

Requirements

Treat each user's cohort week as the week of signup_date, using DATE_TRUNC('week', signup_date).
For each cohort week, count how many users were active in week 0, week 1, and week 2 after signup.
Return the number of retained users and the retention rate for each cohort week and week number.
Only include users whose signup_date is within the last 90 days relative to DATE '2024-06-30'.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user created an account
acquisition_channel	VARCHAR(50)	Marketing acquisition source

`user_activity`

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

Problem

StreamWave wants to measure early user retention by signup cohort. Write a SQL query to calculate week-over-week retention for users who signed up in the last 90 days.

Requirements

Treat each user's cohort week as the week of signup_date, using DATE_TRUNC('week', signup_date).
For each cohort week, count how many users were active in week 0, week 1, and week 2 after signup.
Return the number of retained users and the retention rate for each cohort week and week number.
Only include users whose signup_date is within the last 90 days relative to DATE '2024-06-30'.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user created an account
acquisition_channel	VARCHAR(50)	Marketing acquisition source

`user_activity`

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

Problem

StreamWave wants to measure early user retention by signup cohort. Write a SQL query to calculate week-over-week retention for users who signed up in the last 90 days.

Requirements

Treat each user's cohort week as the week of signup_date, using DATE_TRUNC('week', signup_date).
For each cohort week, count how many users were active in week 0, week 1, and week 2 after signup.
Return the number of retained users and the retention rate for each cohort week and week number.
Only include users whose signup_date is within the last 90 days relative to DATE '2024-06-30'.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user created an account
acquisition_channel	VARCHAR(50)	Marketing acquisition source

`user_activity`

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

Problem

StreamWave wants to measure early user retention by signup cohort. Write a SQL query to calculate week-over-week retention for users who signed up in the last 90 days.

Requirements

Treat each user's cohort week as the week of signup_date, using DATE_TRUNC('week', signup_date).
For each cohort week, count how many users were active in week 0, week 1, and week 2 after signup.
Return the number of retained users and the retention rate for each cohort week and week number.
Only include users whose signup_date is within the last 90 days relative to DATE '2024-06-30'.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user created an account
acquisition_channel	VARCHAR(50)	Marketing acquisition source

`user_activity`

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