Interview Guides

Asked 2mo ago|

Amazon Development Center U.S.

Compare User Retention by Cohort

MediumWindow FunctionsDate FunctionsCTEs

Problem

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

Problem

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

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

Compare Monthly User Retention CohortsMedium

Next question

Asked 2mo ago|

Amazon Development Center U.S.

Compare User Retention by Cohort

MediumWindow FunctionsDate FunctionsCTEs

Problem

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

Problem

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

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

Compare Monthly User Retention CohortsMedium

Next question

Compare User Retention by Cohort | Dataford Interview Questions