Interview Guides

Compare Conversion Rates by Channel | Dataford Interview Questions - Dataford - Ace your Interview

Compare Conversion Rates by Channel

Easy

SQL & Data Manipulation

Asked at 1 company1AggregationsGroup ByCase When

Also asked at

Problem

Task

Rippling’s growth team wants to compare signup conversion performance across acquisition channels for a recent campaign. Write a SQL query to calculate conversion rates by channel using a single event table.

Requirements

For each channel, count how many users had a visit event.
Count how many users had a signup event.
Return channel, visits, signups, and conversion_rate where conversion_rate = signups / visits.
Exclude rows where channel is NULL.
Order results by conversion_rate descending, then channel ascending.

Table Definition

rippling_growth_events

column	type	description
event_id	INT	Unique event row ID
user_id	INT	User identifier
channel	VARCHAR(50)	Acquisition channel tied to the event
event_type	VARCHAR(20)	Event type such as visit or signup
event_date	DATE	Date of the event

Sample Data

event_id	user_id	channel	event_type	event_date
1	101	Paid Search	visit	2024-06-03
2	102	Organic Search	visit	2024-06-01
3	101	Paid Search	signup	2024-06-04
4	103	Partner Referral	visit	2024-06-02
5	104	Paid Social	visit	2024-06-01
6	104	Paid Social	signup	2024-06-02
7	105	Paid Social	visit	2024-06-03
8	106	Organic Search	signup	2024-06-05

Expected Output

channel	visits	signups	conversion_rate
Paid Search	2	1	0.5000
Paid Social	3	1	0.3333
Partner Referral	2	0	0.0000
Organic Search	2	0	0.0000

Schema

rippling_growth_events

Column	Type	Description
event_idPK	INT	Unique event row ID
user_id	INT	User identifier
channel	VARCHAR(50)	Acquisition channel associated with the event
event_type	VARCHAR(20)	Event type such as visit or signup
event_date	DATE	Date the event occurred

Sample Data

Example 1

Input

{"rippling_growth_events":[[1,101,"Paid Search","visit","2024-06-03"],[2,102,"Organic Search","visit","2024-06-01"],[3,101,"Paid Search","signup","2024-06-04"],[4,103,"Partner Referral","visit","2024-06-02"],[5,104,"Paid Social","visit","2024-06-01"],[6,104,"Paid Social","signup","2024-06-02"],[7,105,"Paid Social","visit","2024-06-03"],[8,106,"Organic Search","signup","2024-06-05"],[9,107,null,"visit","2024-06-02"],[10,108,"Partner Referral","visit","2024-06-05"],[11,109,"Paid Search","visit","2

Output

[["Direct","0","0","null"],["Organic Search","1","1","1.0000"],["Paid Search","2","1","0.5000"],["Paid Social","2","1","0.5000"],["Partner Referral","2","0","0.0000"]]

Problem

Task

Requirements

For each channel, count how many users had a visit event.
Count how many users had a signup event.
Return channel, visits, signups, and conversion_rate where conversion_rate = signups / visits.
Exclude rows where channel is NULL.
Order results by conversion_rate descending, then channel ascending.

Table Definition

rippling_growth_events

column	type	description
event_id	INT	Unique event row ID
user_id	INT	User identifier
channel	VARCHAR(50)	Acquisition channel tied to the event
event_type	VARCHAR(20)	Event type such as visit or signup
event_date	DATE	Date of the event

Sample Data

event_id	user_id	channel	event_type	event_date
1	101	Paid Search	visit	2024-06-03
2	102	Organic Search	visit	2024-06-01
3	101	Paid Search	signup	2024-06-04
4	103	Partner Referral	visit	2024-06-02
5	104	Paid Social	visit	2024-06-01
6	104	Paid Social	signup	2024-06-02
7	105	Paid Social	visit	2024-06-03
8	106	Organic Search	signup	2024-06-05

Expected Output

channel	visits	signups	conversion_rate
Paid Search	2	1	0.5000
Paid Social	3	1	0.3333
Partner Referral	2	0	0.0000
Organic Search	2	0	0.0000

Schema

rippling_growth_events

Column	Type	Description
event_idPK	INT	Unique event row ID
user_id	INT	User identifier
channel	VARCHAR(50)	Acquisition channel associated with the event
event_type	VARCHAR(20)	Event type such as visit or signup
event_date	DATE	Date the event occurred

Sample Data

Example 1

Input

{"rippling_growth_events":[[1,101,"Paid Search","visit","2024-06-03"],[2,102,"Organic Search","visit","2024-06-01"],[3,101,"Paid Search","signup","2024-06-04"],[4,103,"Partner Referral","visit","2024-06-02"],[5,104,"Paid Social","visit","2024-06-01"],[6,104,"Paid Social","signup","2024-06-02"],[7,105,"Paid Social","visit","2024-06-03"],[8,106,"Organic Search","signup","2024-06-05"],[9,107,null,"visit","2024-06-02"],[10,108,"Partner Referral","visit","2024-06-05"],[11,109,"Paid Search","visit","2

Output

[["Direct","0","0","null"],["Organic Search","1","1","1.0000"],["Paid Search","2","1","0.5000"],["Paid Social","2","1","0.5000"],["Partner Referral","2","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

Channel Conversion Rate CalculationEasy

Compare Chime Channel Conversion RatesMedium

Next question

Compare Conversion Rates by Channel

Easy

SQL & Data Manipulation

Asked at 1 company1AggregationsGroup ByCase When

Also asked at

Problem

Task

Requirements

For each channel, count how many users had a visit event.
Count how many users had a signup event.
Return channel, visits, signups, and conversion_rate where conversion_rate = signups / visits.
Exclude rows where channel is NULL.
Order results by conversion_rate descending, then channel ascending.

Table Definition

rippling_growth_events

column	type	description
event_id	INT	Unique event row ID
user_id	INT	User identifier
channel	VARCHAR(50)	Acquisition channel tied to the event
event_type	VARCHAR(20)	Event type such as visit or signup
event_date	DATE	Date of the event

Sample Data

event_id	user_id	channel	event_type	event_date
1	101	Paid Search	visit	2024-06-03
2	102	Organic Search	visit	2024-06-01
3	101	Paid Search	signup	2024-06-04
4	103	Partner Referral	visit	2024-06-02
5	104	Paid Social	visit	2024-06-01
6	104	Paid Social	signup	2024-06-02
7	105	Paid Social	visit	2024-06-03
8	106	Organic Search	signup	2024-06-05

Expected Output

channel	visits	signups	conversion_rate
Paid Search	2	1	0.5000
Paid Social	3	1	0.3333
Partner Referral	2	0	0.0000
Organic Search	2	0	0.0000

Schema

rippling_growth_events

Column	Type	Description
event_idPK	INT	Unique event row ID
user_id	INT	User identifier
channel	VARCHAR(50)	Acquisition channel associated with the event
event_type	VARCHAR(20)	Event type such as visit or signup
event_date	DATE	Date the event occurred

Sample Data

Example 1

Input

{"rippling_growth_events":[[1,101,"Paid Search","visit","2024-06-03"],[2,102,"Organic Search","visit","2024-06-01"],[3,101,"Paid Search","signup","2024-06-04"],[4,103,"Partner Referral","visit","2024-06-02"],[5,104,"Paid Social","visit","2024-06-01"],[6,104,"Paid Social","signup","2024-06-02"],[7,105,"Paid Social","visit","2024-06-03"],[8,106,"Organic Search","signup","2024-06-05"],[9,107,null,"visit","2024-06-02"],[10,108,"Partner Referral","visit","2024-06-05"],[11,109,"Paid Search","visit","2

Output

[["Direct","0","0","null"],["Organic Search","1","1","1.0000"],["Paid Search","2","1","0.5000"],["Paid Social","2","1","0.5000"],["Partner Referral","2","0","0.0000"]]

Problem

Task

Requirements

For each channel, count how many users had a visit event.
Count how many users had a signup event.
Return channel, visits, signups, and conversion_rate where conversion_rate = signups / visits.
Exclude rows where channel is NULL.
Order results by conversion_rate descending, then channel ascending.

Table Definition

rippling_growth_events

column	type	description
event_id	INT	Unique event row ID
user_id	INT	User identifier
channel	VARCHAR(50)	Acquisition channel tied to the event
event_type	VARCHAR(20)	Event type such as visit or signup
event_date	DATE	Date of the event

Sample Data

event_id	user_id	channel	event_type	event_date
1	101	Paid Search	visit	2024-06-03
2	102	Organic Search	visit	2024-06-01
3	101	Paid Search	signup	2024-06-04
4	103	Partner Referral	visit	2024-06-02
5	104	Paid Social	visit	2024-06-01
6	104	Paid Social	signup	2024-06-02
7	105	Paid Social	visit	2024-06-03
8	106	Organic Search	signup	2024-06-05

Expected Output

channel	visits	signups	conversion_rate
Paid Search	2	1	0.5000
Paid Social	3	1	0.3333
Partner Referral	2	0	0.0000
Organic Search	2	0	0.0000

Schema

rippling_growth_events

Column	Type	Description
event_idPK	INT	Unique event row ID
user_id	INT	User identifier
channel	VARCHAR(50)	Acquisition channel associated with the event
event_type	VARCHAR(20)	Event type such as visit or signup
event_date	DATE	Date the event occurred

Sample Data

Example 1

Input

{"rippling_growth_events":[[1,101,"Paid Search","visit","2024-06-03"],[2,102,"Organic Search","visit","2024-06-01"],[3,101,"Paid Search","signup","2024-06-04"],[4,103,"Partner Referral","visit","2024-06-02"],[5,104,"Paid Social","visit","2024-06-01"],[6,104,"Paid Social","signup","2024-06-02"],[7,105,"Paid Social","visit","2024-06-03"],[8,106,"Organic Search","signup","2024-06-05"],[9,107,null,"visit","2024-06-02"],[10,108,"Partner Referral","visit","2024-06-05"],[11,109,"Paid Search","visit","2

Output

[["Direct","0","0","null"],["Organic Search","1","1","1.0000"],["Paid Search","2","1","0.5000"],["Paid Social","2","1","0.5000"],["Partner Referral","2","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

Channel Conversion Rate CalculationEasy

Compare Chime Channel Conversion RatesMedium

Next question