Interview Guides

Channel Conversion Rate Calculation | Dataford Interview Questions - Dataford - Ace your Interview

Channel Conversion Rate Calculation

Easy

SQL & Data Manipulation

Asked at 2 companies2JoinsAggregationsCase When

Also asked at

Problem

Task

HelloFresh wants a simple view of signup conversion performance by acquisition channel in the US marketing funnel. Write a SQL query to calculate conversion rate by acquisition_channel from a single table.

Requirements

Return one row per acquisition_channel.
Count total visitors and total converted visitors, where a visitor is considered converted when converted_to_subscriber = TRUE.
Calculate conversion rate as converted_visitors / total_visitors, rounded to 4 decimal places.
Exclude rows where acquisition_channel is NULL.
Order results by conversion rate descending, then by acquisition channel ascending.

Table Definition

Table: hellofresh_web_sessions

column	type	description
session_id	INT	Unique session identifier
visitor_id	INT	Visitor identifier
acquisition_channel	VARCHAR(50)	Marketing acquisition source
session_date	DATE	Date of the visit
converted_to_subscriber	BOOLEAN	Whether the visitor subscribed in that session

Sample Data

session_id	visitor_id	acquisition_channel	session_date	converted_to_subscriber
5	1005	Paid Search	2024-01-02	false
1	1001	Paid Social	2024-01-03	true
9	1009	Referral	2024-01-04	true
3	1003	Organic Search	2024-01-02	false
11	1011	Email	2024-01-06	true
2	1002	Paid Social	2024-01-01	false
8	1008	Referral	2024-01-03	false
6	1006	Paid Search	2024-01-05	true
4	1004	Organic Search	2024-01-04	false
10	1010	NULL	2024-01-05	true

Expected Output

acquisition_channel	total_visitors	converted_visitors	conversion_rate
Email	2	1	0.5000
Referral	2	1	0.5000
Paid Search	3	1	0.3333
Paid Social	3	1	0.3333
Organic Search	2	0	0.0000

Schema

hellofresh_web_sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
visitor_id	INT	Unique visitor identifier
acquisition_channel	VARCHAR(50)	Marketing channel that brought the visitor to HelloFresh
session_date	DATE	Date of the web session
converted_to_subscriber	BOOLEAN	Whether the visitor subscribed during the session

Sample Data

Example 1

Input

{"hellofresh_web_sessions":[[5,1005,"Paid Search","2024-01-02",false],[1,1001,"Paid Social","2024-01-03",true],[9,1009,"Referral","2024-01-04",true],[3,1003,"Organic Search","2024-01-02",false],[11,1011,"Email","2024-01-06",true],[2,1002,"Paid Social","2024-01-01",false],[8,1008,"Referral","2024-01-03",false],[6,1006,"Paid Search","2024-01-05",true],[4,1004,"Organic Search","2024-01-04",false],[10,1010,null,"2024-01-05",true],[7,1007,"Paid Search","2024-01-01",false],[12,1012,"Email","2024-01-02

Output

[["Email","2","1","0.5000"],["Paid Social","2","1","0.5000"],["Referral","2","1","0.5000"],["Paid Search","3","1","0.3333"],["Organic Search","2","0","0.0000"],["Push Notification","1","0","0.0000"]]

Problem

Task

Requirements

Return one row per acquisition_channel.
Count total visitors and total converted visitors, where a visitor is considered converted when converted_to_subscriber = TRUE.
Calculate conversion rate as converted_visitors / total_visitors, rounded to 4 decimal places.
Exclude rows where acquisition_channel is NULL.
Order results by conversion rate descending, then by acquisition channel ascending.

Table Definition

Table: hellofresh_web_sessions

column	type	description
session_id	INT	Unique session identifier
visitor_id	INT	Visitor identifier
acquisition_channel	VARCHAR(50)	Marketing acquisition source
session_date	DATE	Date of the visit
converted_to_subscriber	BOOLEAN	Whether the visitor subscribed in that session

Sample Data

session_id	visitor_id	acquisition_channel	session_date	converted_to_subscriber
5	1005	Paid Search	2024-01-02	false
1	1001	Paid Social	2024-01-03	true
9	1009	Referral	2024-01-04	true
3	1003	Organic Search	2024-01-02	false
11	1011	Email	2024-01-06	true
2	1002	Paid Social	2024-01-01	false
8	1008	Referral	2024-01-03	false
6	1006	Paid Search	2024-01-05	true
4	1004	Organic Search	2024-01-04	false
10	1010	NULL	2024-01-05	true

Expected Output

acquisition_channel	total_visitors	converted_visitors	conversion_rate
Email	2	1	0.5000
Referral	2	1	0.5000
Paid Search	3	1	0.3333
Paid Social	3	1	0.3333
Organic Search	2	0	0.0000

Schema

hellofresh_web_sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
visitor_id	INT	Unique visitor identifier
acquisition_channel	VARCHAR(50)	Marketing channel that brought the visitor to HelloFresh
session_date	DATE	Date of the web session
converted_to_subscriber	BOOLEAN	Whether the visitor subscribed during the session

Sample Data

Example 1

Input

{"hellofresh_web_sessions":[[5,1005,"Paid Search","2024-01-02",false],[1,1001,"Paid Social","2024-01-03",true],[9,1009,"Referral","2024-01-04",true],[3,1003,"Organic Search","2024-01-02",false],[11,1011,"Email","2024-01-06",true],[2,1002,"Paid Social","2024-01-01",false],[8,1008,"Referral","2024-01-03",false],[6,1006,"Paid Search","2024-01-05",true],[4,1004,"Organic Search","2024-01-04",false],[10,1010,null,"2024-01-05",true],[7,1007,"Paid Search","2024-01-01",false],[12,1012,"Email","2024-01-02

Output

[["Email","2","1","0.5000"],["Paid Social","2","1","0.5000"],["Referral","2","1","0.5000"],["Paid Search","3","1","0.3333"],["Organic Search","2","0","0.0000"],["Push Notification","1","0","0.0000"]]

Practice SQL

PostgreSQL

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

Up next

Conversion Rate by Acquisition ChannelMedium

Conversion Rate by Acquisition ChannelMedium

Compare Conversion Rates by ChannelEasy

Next question

Channel Conversion Rate Calculation

Easy

SQL & Data Manipulation

Asked at 2 companies2JoinsAggregationsCase When

Also asked at

Problem

Task

Requirements

Return one row per acquisition_channel.
Count total visitors and total converted visitors, where a visitor is considered converted when converted_to_subscriber = TRUE.
Calculate conversion rate as converted_visitors / total_visitors, rounded to 4 decimal places.
Exclude rows where acquisition_channel is NULL.
Order results by conversion rate descending, then by acquisition channel ascending.

Table Definition

Table: hellofresh_web_sessions

column	type	description
session_id	INT	Unique session identifier
visitor_id	INT	Visitor identifier
acquisition_channel	VARCHAR(50)	Marketing acquisition source
session_date	DATE	Date of the visit
converted_to_subscriber	BOOLEAN	Whether the visitor subscribed in that session

Sample Data

session_id	visitor_id	acquisition_channel	session_date	converted_to_subscriber
5	1005	Paid Search	2024-01-02	false
1	1001	Paid Social	2024-01-03	true
9	1009	Referral	2024-01-04	true
3	1003	Organic Search	2024-01-02	false
11	1011	Email	2024-01-06	true
2	1002	Paid Social	2024-01-01	false
8	1008	Referral	2024-01-03	false
6	1006	Paid Search	2024-01-05	true
4	1004	Organic Search	2024-01-04	false
10	1010	NULL	2024-01-05	true

Expected Output

acquisition_channel	total_visitors	converted_visitors	conversion_rate
Email	2	1	0.5000
Referral	2	1	0.5000
Paid Search	3	1	0.3333
Paid Social	3	1	0.3333
Organic Search	2	0	0.0000

Schema

hellofresh_web_sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
visitor_id	INT	Unique visitor identifier
acquisition_channel	VARCHAR(50)	Marketing channel that brought the visitor to HelloFresh
session_date	DATE	Date of the web session
converted_to_subscriber	BOOLEAN	Whether the visitor subscribed during the session

Sample Data

Example 1

Input

{"hellofresh_web_sessions":[[5,1005,"Paid Search","2024-01-02",false],[1,1001,"Paid Social","2024-01-03",true],[9,1009,"Referral","2024-01-04",true],[3,1003,"Organic Search","2024-01-02",false],[11,1011,"Email","2024-01-06",true],[2,1002,"Paid Social","2024-01-01",false],[8,1008,"Referral","2024-01-03",false],[6,1006,"Paid Search","2024-01-05",true],[4,1004,"Organic Search","2024-01-04",false],[10,1010,null,"2024-01-05",true],[7,1007,"Paid Search","2024-01-01",false],[12,1012,"Email","2024-01-02

Output

[["Email","2","1","0.5000"],["Paid Social","2","1","0.5000"],["Referral","2","1","0.5000"],["Paid Search","3","1","0.3333"],["Organic Search","2","0","0.0000"],["Push Notification","1","0","0.0000"]]

Problem

Task

Requirements

Return one row per acquisition_channel.
Count total visitors and total converted visitors, where a visitor is considered converted when converted_to_subscriber = TRUE.
Calculate conversion rate as converted_visitors / total_visitors, rounded to 4 decimal places.
Exclude rows where acquisition_channel is NULL.
Order results by conversion rate descending, then by acquisition channel ascending.

Table Definition

Table: hellofresh_web_sessions

column	type	description
session_id	INT	Unique session identifier
visitor_id	INT	Visitor identifier
acquisition_channel	VARCHAR(50)	Marketing acquisition source
session_date	DATE	Date of the visit
converted_to_subscriber	BOOLEAN	Whether the visitor subscribed in that session

Sample Data

session_id	visitor_id	acquisition_channel	session_date	converted_to_subscriber
5	1005	Paid Search	2024-01-02	false
1	1001	Paid Social	2024-01-03	true
9	1009	Referral	2024-01-04	true
3	1003	Organic Search	2024-01-02	false
11	1011	Email	2024-01-06	true
2	1002	Paid Social	2024-01-01	false
8	1008	Referral	2024-01-03	false
6	1006	Paid Search	2024-01-05	true
4	1004	Organic Search	2024-01-04	false
10	1010	NULL	2024-01-05	true

Expected Output

acquisition_channel	total_visitors	converted_visitors	conversion_rate
Email	2	1	0.5000
Referral	2	1	0.5000
Paid Search	3	1	0.3333
Paid Social	3	1	0.3333
Organic Search	2	0	0.0000

Schema

hellofresh_web_sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
visitor_id	INT	Unique visitor identifier
acquisition_channel	VARCHAR(50)	Marketing channel that brought the visitor to HelloFresh
session_date	DATE	Date of the web session
converted_to_subscriber	BOOLEAN	Whether the visitor subscribed during the session

Sample Data

Example 1

Input

{"hellofresh_web_sessions":[[5,1005,"Paid Search","2024-01-02",false],[1,1001,"Paid Social","2024-01-03",true],[9,1009,"Referral","2024-01-04",true],[3,1003,"Organic Search","2024-01-02",false],[11,1011,"Email","2024-01-06",true],[2,1002,"Paid Social","2024-01-01",false],[8,1008,"Referral","2024-01-03",false],[6,1006,"Paid Search","2024-01-05",true],[4,1004,"Organic Search","2024-01-04",false],[10,1010,null,"2024-01-05",true],[7,1007,"Paid Search","2024-01-01",false],[12,1012,"Email","2024-01-02

Output

[["Email","2","1","0.5000"],["Paid Social","2","1","0.5000"],["Referral","2","1","0.5000"],["Paid Search","3","1","0.3333"],["Organic Search","2","0","0.0000"],["Push Notification","1","0","0.0000"]]

Practice SQL

PostgreSQL

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

Up next

Conversion Rate by Acquisition ChannelMedium

Compare Conversion Rates by ChannelEasy

Next question