Channel Conversion Rate Calculation | Dataford Interview Questions

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

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

Problem

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

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