Interview Guides

Compare Retention by Acquisition Source | Dataford Interview Questions - Dataford - Ace your Interview

Compare Retention by Acquisition Source

Medium

SQL & Data Manipulation

Asked at 2 companies2JoinsGroup ByDate Functions

Also asked at

Problem

Task

NovaPlay wants to compare user retention across acquisition sources. Write a PostgreSQL query to calculate month-1 retention by acquisition source.

A user is considered retained in month 1 if they have at least one session in the calendar month immediately after their signup month.

Requirements

For each acquisition source, count total signed-up users.
Count how many of those users were retained in month 1.
Calculate retention rate as retained_users / total_users, rounded to 4 decimal places.
Include users with NULL acquisition sources and label them as 'Unknown'.
Return results ordered by retention rate descending, then acquisition source ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_source	VARCHAR(50)	Signup source such as Paid Search or Organic

`sessions`

column	type	description
session_id	INT	Unique session identifier
user_id	INT	User tied to the session
session_date	DATE	Date of the session
device_type	VARCHAR(20)	Device used for the session

Sample Data

`users`

user_id	signup_date	acquisition_source
4	2024-02-20	Referral
1	2024-01-15	Paid Search
7	2024-03-15	NULL
2	2024-01-28	Organic

`sessions`

session_id	user_id	session_date	device_type
105	1	2024-02-10	Web
101	1	2024-01-20	iOS
110	4	2024-03-01	Android
116	12	2024-03-10	Web

Expected Output

acquisition_source	total_users	retained_users	retention_rate
Paid Search	3	2	0.6667
Referral	3	2	0.6667
Organic	3	1	0.3333
Unknown	2	0	0.0000

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_source	VARCHAR(50)	Marketing or referral source that acquired the user

sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
user_id	INT	User associated with the session
session_date	DATE	Date the session occurred
device_type	VARCHAR(20)	Device used during the session

Sample Data

Example 1

Input

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

Output

[["Referral","3","3","1.0000"],["Paid Search","4","3","0.7500"],["Organic","3","2","0.6667"],["Unknown","3","1","0.3333"],["Affiliate","1","0","0.0000"],["App Store","1","0","0.0000"],["Brand Campaign","1","0","0.0000"],["Community","1","0","0.0000"],["Content Marketing","1","0","0.0000"],["Direct","1","0","0.0000"],["Facebook Ads","1","0","0.0000"],["Influencer","1","0","0.0000"],["Instagram Ads","1","0","0.0000"],["LinkedIn","2","0","0.0000"],["Out of Home","1","0","0.0000"],["Reseller","2","0

Problem

Task

NovaPlay wants to compare user retention across acquisition sources. Write a PostgreSQL query to calculate month-1 retention by acquisition source.

A user is considered retained in month 1 if they have at least one session in the calendar month immediately after their signup month.

Requirements

For each acquisition source, count total signed-up users.
Count how many of those users were retained in month 1.
Calculate retention rate as retained_users / total_users, rounded to 4 decimal places.
Include users with NULL acquisition sources and label them as 'Unknown'.
Return results ordered by retention rate descending, then acquisition source ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_source	VARCHAR(50)	Signup source such as Paid Search or Organic

`sessions`

column	type	description
session_id	INT	Unique session identifier
user_id	INT	User tied to the session
session_date	DATE	Date of the session
device_type	VARCHAR(20)	Device used for the session

Sample Data

`users`

user_id	signup_date	acquisition_source
4	2024-02-20	Referral
1	2024-01-15	Paid Search
7	2024-03-15	NULL
2	2024-01-28	Organic

`sessions`

session_id	user_id	session_date	device_type
105	1	2024-02-10	Web
101	1	2024-01-20	iOS
110	4	2024-03-01	Android
116	12	2024-03-10	Web

Expected Output

acquisition_source	total_users	retained_users	retention_rate
Paid Search	3	2	0.6667
Referral	3	2	0.6667
Organic	3	1	0.3333
Unknown	2	0	0.0000

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_source	VARCHAR(50)	Marketing or referral source that acquired the user

sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
user_id	INT	User associated with the session
session_date	DATE	Date the session occurred
device_type	VARCHAR(20)	Device used during the session

Sample Data

Example 1

Input

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

Output

[["Referral","3","3","1.0000"],["Paid Search","4","3","0.7500"],["Organic","3","2","0.6667"],["Unknown","3","1","0.3333"],["Affiliate","1","0","0.0000"],["App Store","1","0","0.0000"],["Brand Campaign","1","0","0.0000"],["Community","1","0","0.0000"],["Content Marketing","1","0","0.0000"],["Direct","1","0","0.0000"],["Facebook Ads","1","0","0.0000"],["Influencer","1","0","0.0000"],["Instagram Ads","1","0","0.0000"],["LinkedIn","2","0","0.0000"],["Out of Home","1","0","0.0000"],["Reseller","2","0

Practice SQL

PostgreSQL

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

Up next

30-Day Retention by ChannelMedium

Measure Retention After Product LaunchMedium

Compare Channel Retention by CohortMedium

Next question

Compare Retention by Acquisition Source

Medium

SQL & Data Manipulation

Asked at 2 companies2JoinsGroup ByDate Functions

Also asked at

Problem

Task

NovaPlay wants to compare user retention across acquisition sources. Write a PostgreSQL query to calculate month-1 retention by acquisition source.

A user is considered retained in month 1 if they have at least one session in the calendar month immediately after their signup month.

Requirements

For each acquisition source, count total signed-up users.
Count how many of those users were retained in month 1.
Calculate retention rate as retained_users / total_users, rounded to 4 decimal places.
Include users with NULL acquisition sources and label them as 'Unknown'.
Return results ordered by retention rate descending, then acquisition source ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_source	VARCHAR(50)	Signup source such as Paid Search or Organic

`sessions`

column	type	description
session_id	INT	Unique session identifier
user_id	INT	User tied to the session
session_date	DATE	Date of the session
device_type	VARCHAR(20)	Device used for the session

Sample Data

`users`

user_id	signup_date	acquisition_source
4	2024-02-20	Referral
1	2024-01-15	Paid Search
7	2024-03-15	NULL
2	2024-01-28	Organic

`sessions`

session_id	user_id	session_date	device_type
105	1	2024-02-10	Web
101	1	2024-01-20	iOS
110	4	2024-03-01	Android
116	12	2024-03-10	Web

Expected Output

acquisition_source	total_users	retained_users	retention_rate
Paid Search	3	2	0.6667
Referral	3	2	0.6667
Organic	3	1	0.3333
Unknown	2	0	0.0000

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_source	VARCHAR(50)	Marketing or referral source that acquired the user

sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
user_id	INT	User associated with the session
session_date	DATE	Date the session occurred
device_type	VARCHAR(20)	Device used during the session

Sample Data

Example 1

Input

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

Output

[["Referral","3","3","1.0000"],["Paid Search","4","3","0.7500"],["Organic","3","2","0.6667"],["Unknown","3","1","0.3333"],["Affiliate","1","0","0.0000"],["App Store","1","0","0.0000"],["Brand Campaign","1","0","0.0000"],["Community","1","0","0.0000"],["Content Marketing","1","0","0.0000"],["Direct","1","0","0.0000"],["Facebook Ads","1","0","0.0000"],["Influencer","1","0","0.0000"],["Instagram Ads","1","0","0.0000"],["LinkedIn","2","0","0.0000"],["Out of Home","1","0","0.0000"],["Reseller","2","0

Problem

Task

NovaPlay wants to compare user retention across acquisition sources. Write a PostgreSQL query to calculate month-1 retention by acquisition source.

A user is considered retained in month 1 if they have at least one session in the calendar month immediately after their signup month.

Requirements

For each acquisition source, count total signed-up users.
Count how many of those users were retained in month 1.
Calculate retention rate as retained_users / total_users, rounded to 4 decimal places.
Include users with NULL acquisition sources and label them as 'Unknown'.
Return results ordered by retention rate descending, then acquisition source ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_source	VARCHAR(50)	Signup source such as Paid Search or Organic

`sessions`

column	type	description
session_id	INT	Unique session identifier
user_id	INT	User tied to the session
session_date	DATE	Date of the session
device_type	VARCHAR(20)	Device used for the session

Sample Data

`users`

user_id	signup_date	acquisition_source
4	2024-02-20	Referral
1	2024-01-15	Paid Search
7	2024-03-15	NULL
2	2024-01-28	Organic

`sessions`

session_id	user_id	session_date	device_type
105	1	2024-02-10	Web
101	1	2024-01-20	iOS
110	4	2024-03-01	Android
116	12	2024-03-10	Web

Expected Output

acquisition_source	total_users	retained_users	retention_rate
Paid Search	3	2	0.6667
Referral	3	2	0.6667
Organic	3	1	0.3333
Unknown	2	0	0.0000

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_source	VARCHAR(50)	Marketing or referral source that acquired the user

sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
user_id	INT	User associated with the session
session_date	DATE	Date the session occurred
device_type	VARCHAR(20)	Device used during the session

Sample Data

Example 1

Input

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

Output

[["Referral","3","3","1.0000"],["Paid Search","4","3","0.7500"],["Organic","3","2","0.6667"],["Unknown","3","1","0.3333"],["Affiliate","1","0","0.0000"],["App Store","1","0","0.0000"],["Brand Campaign","1","0","0.0000"],["Community","1","0","0.0000"],["Content Marketing","1","0","0.0000"],["Direct","1","0","0.0000"],["Facebook Ads","1","0","0.0000"],["Influencer","1","0","0.0000"],["Instagram Ads","1","0","0.0000"],["LinkedIn","2","0","0.0000"],["Out of Home","1","0","0.0000"],["Reseller","2","0

Practice SQL

PostgreSQL

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

Up next

30-Day Retention by ChannelMedium

Measure Retention After Product LaunchMedium

Compare Channel Retention by CohortMedium

Next question