Interview Guides

Conversion Rate by Channel Segment | Dataford Interview Questions - Dataford - Ace your Interview

Conversion Rate by Channel Segment

Medium

SQL & Data Manipulation

Asked at 4 companies4JoinsGroup ByCase When

Also asked at

Problem

Task

BrightCart wants to measure how efficiently each acquisition channel converts users across customer segments. Write a PostgreSQL query to calculate conversion rate by channel and segment for users who signed up in January 2024.

A user is considered converted if they have at least one row in conversions with is_conversion = true within 30 days of their signup date.

Requirements

Return one row per channel and segment.
Count total signed-up users and converted users.
Compute conversion rate as converted_users / total_users, rounded to 4 decimal places.
Exclude users whose segment is NULL.
Order results by conversion rate descending, then by channel and segment ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user signed up
channel	VARCHAR(50)	Acquisition channel
segment	VARCHAR(50)	Customer segment

`conversions`

column	type	description
conversion_id	INT	Unique conversion event identifier
user_id	INT	User tied to the conversion event
conversion_date	DATE	Date of conversion event
is_conversion	BOOLEAN	Whether the event counts as a conversion

`channels`

column	type	description
channel	VARCHAR(50)	Channel name
channel_group	VARCHAR(50)	Higher-level channel grouping

Sample Data

`users`

user_id	signup_date	channel	segment
1	2024-01-05	Paid Search	SMB
2	2024-01-07	Organic Search	Enterprise
3	2024-01-10	Paid Social	SMB
4	2024-01-12	Email	Mid-Market

`conversions`

conversion_id	user_id	conversion_date	is_conversion
101	1	2024-01-20	true
102	1	2024-02-02	true
103	2	2024-02-15	true
104	3	2024-01-25	false

Expected Output

channel	segment	total_users	converted_users	conversion_rate
Email	Mid-Market	2	2	1.0000
Organic Search	SMB	1	1	1.0000
Paid Search	SMB	2	1	0.5000
Paid Social	SMB	2	1	0.5000
Organic Search	Enterprise	2	0	0.0000

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
channel	VARCHAR(50)	Acquisition channel for the signup
segment	VARCHAR(50)	Customer segment

conversions

Column	Type	Description
conversion_idPK	INT	Unique conversion event identifier
user_id	INT	User associated with the event
conversion_date	DATE	Date the conversion event occurred
is_conversion	BOOLEAN	Whether the event should count as a conversion

channels

Column	Type	Description
channelPK	VARCHAR(50)	Channel name
channel_group	VARCHAR(50)	Higher-level grouping of the channel

Sample Data

Example 1

Input

{"users":[[1,"2024-01-05","Paid Search","SMB"],[2,"2024-01-07","Organic Search","Enterprise"],[3,"2024-01-10","Paid Social","SMB"],[4,"2024-01-12","Email","Mid-Market"],[5,"2024-01-15","Paid Search","SMB"],[6,"2024-01-18","Organic Search","SMB"],[7,"2024-01-20","Email","Mid-Market"],[8,"2024-01-22","Paid Social","SMB"],[9,"2024-01-25","Organic Search","Enterprise"],[10,"2024-01-28","Referral",null],[11,"2023-12-30","Paid Search","Enterprise"],[12,"2024-01-09",null,"SMB"],["1","2024-01-02","Organ

Output

[["Email","Mid-Market","2","2","1.0000"],["Organic Search","SMB","1","1","1.0000"],["Paid Search","SMB","2","1","0.5000"],["Email","Developer","1","0","0.0000"],["Organic Search","Enterprise","2","0","0.0000"],["Organic Search","Strategic","1","0","0.0000"],["Paid Search","Trial","1","0","0.0000"],["Paid Social","SMB","2","0","0.0000"],["null","SMB","1","0","0.0000"]]

Problem

Task

A user is considered converted if they have at least one row in conversions with is_conversion = true within 30 days of their signup date.

Requirements

Return one row per channel and segment.
Count total signed-up users and converted users.
Compute conversion rate as converted_users / total_users, rounded to 4 decimal places.
Exclude users whose segment is NULL.
Order results by conversion rate descending, then by channel and segment ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user signed up
channel	VARCHAR(50)	Acquisition channel
segment	VARCHAR(50)	Customer segment

`conversions`

column	type	description
conversion_id	INT	Unique conversion event identifier
user_id	INT	User tied to the conversion event
conversion_date	DATE	Date of conversion event
is_conversion	BOOLEAN	Whether the event counts as a conversion

`channels`

column	type	description
channel	VARCHAR(50)	Channel name
channel_group	VARCHAR(50)	Higher-level channel grouping

Sample Data

`users`

user_id	signup_date	channel	segment
1	2024-01-05	Paid Search	SMB
2	2024-01-07	Organic Search	Enterprise
3	2024-01-10	Paid Social	SMB
4	2024-01-12	Email	Mid-Market

`conversions`

conversion_id	user_id	conversion_date	is_conversion
101	1	2024-01-20	true
102	1	2024-02-02	true
103	2	2024-02-15	true
104	3	2024-01-25	false

Expected Output

channel	segment	total_users	converted_users	conversion_rate
Email	Mid-Market	2	2	1.0000
Organic Search	SMB	1	1	1.0000
Paid Search	SMB	2	1	0.5000
Paid Social	SMB	2	1	0.5000
Organic Search	Enterprise	2	0	0.0000

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
channel	VARCHAR(50)	Acquisition channel for the signup
segment	VARCHAR(50)	Customer segment

conversions

Column	Type	Description
conversion_idPK	INT	Unique conversion event identifier
user_id	INT	User associated with the event
conversion_date	DATE	Date the conversion event occurred
is_conversion	BOOLEAN	Whether the event should count as a conversion

channels

Column	Type	Description
channelPK	VARCHAR(50)	Channel name
channel_group	VARCHAR(50)	Higher-level grouping of the channel

Sample Data

Example 1

Input

{"users":[[1,"2024-01-05","Paid Search","SMB"],[2,"2024-01-07","Organic Search","Enterprise"],[3,"2024-01-10","Paid Social","SMB"],[4,"2024-01-12","Email","Mid-Market"],[5,"2024-01-15","Paid Search","SMB"],[6,"2024-01-18","Organic Search","SMB"],[7,"2024-01-20","Email","Mid-Market"],[8,"2024-01-22","Paid Social","SMB"],[9,"2024-01-25","Organic Search","Enterprise"],[10,"2024-01-28","Referral",null],[11,"2023-12-30","Paid Search","Enterprise"],[12,"2024-01-09",null,"SMB"],["1","2024-01-02","Organ

Output

[["Email","Mid-Market","2","2","1.0000"],["Organic Search","SMB","1","1","1.0000"],["Paid Search","SMB","2","1","0.5000"],["Email","Developer","1","0","0.0000"],["Organic Search","Enterprise","2","0","0.0000"],["Organic Search","Strategic","1","0","0.0000"],["Paid Search","Trial","1","0","0.0000"],["Paid Social","SMB","2","0","0.0000"],["null","SMB","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

Conversion Rate by Channel Segment

Medium

SQL & Data Manipulation

Asked at 4 companies4JoinsGroup ByCase When

Also asked at

Problem

Task

A user is considered converted if they have at least one row in conversions with is_conversion = true within 30 days of their signup date.

Requirements

Return one row per channel and segment.
Count total signed-up users and converted users.
Compute conversion rate as converted_users / total_users, rounded to 4 decimal places.
Exclude users whose segment is NULL.
Order results by conversion rate descending, then by channel and segment ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user signed up
channel	VARCHAR(50)	Acquisition channel
segment	VARCHAR(50)	Customer segment

`conversions`

column	type	description
conversion_id	INT	Unique conversion event identifier
user_id	INT	User tied to the conversion event
conversion_date	DATE	Date of conversion event
is_conversion	BOOLEAN	Whether the event counts as a conversion

`channels`

column	type	description
channel	VARCHAR(50)	Channel name
channel_group	VARCHAR(50)	Higher-level channel grouping

Sample Data

`users`

user_id	signup_date	channel	segment
1	2024-01-05	Paid Search	SMB
2	2024-01-07	Organic Search	Enterprise
3	2024-01-10	Paid Social	SMB
4	2024-01-12	Email	Mid-Market

`conversions`

conversion_id	user_id	conversion_date	is_conversion
101	1	2024-01-20	true
102	1	2024-02-02	true
103	2	2024-02-15	true
104	3	2024-01-25	false

Expected Output

channel	segment	total_users	converted_users	conversion_rate
Email	Mid-Market	2	2	1.0000
Organic Search	SMB	1	1	1.0000
Paid Search	SMB	2	1	0.5000
Paid Social	SMB	2	1	0.5000
Organic Search	Enterprise	2	0	0.0000

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
channel	VARCHAR(50)	Acquisition channel for the signup
segment	VARCHAR(50)	Customer segment

conversions

Column	Type	Description
conversion_idPK	INT	Unique conversion event identifier
user_id	INT	User associated with the event
conversion_date	DATE	Date the conversion event occurred
is_conversion	BOOLEAN	Whether the event should count as a conversion

channels

Column	Type	Description
channelPK	VARCHAR(50)	Channel name
channel_group	VARCHAR(50)	Higher-level grouping of the channel

Sample Data

Example 1

Input

{"users":[[1,"2024-01-05","Paid Search","SMB"],[2,"2024-01-07","Organic Search","Enterprise"],[3,"2024-01-10","Paid Social","SMB"],[4,"2024-01-12","Email","Mid-Market"],[5,"2024-01-15","Paid Search","SMB"],[6,"2024-01-18","Organic Search","SMB"],[7,"2024-01-20","Email","Mid-Market"],[8,"2024-01-22","Paid Social","SMB"],[9,"2024-01-25","Organic Search","Enterprise"],[10,"2024-01-28","Referral",null],[11,"2023-12-30","Paid Search","Enterprise"],[12,"2024-01-09",null,"SMB"],["1","2024-01-02","Organ

Output

[["Email","Mid-Market","2","2","1.0000"],["Organic Search","SMB","1","1","1.0000"],["Paid Search","SMB","2","1","0.5000"],["Email","Developer","1","0","0.0000"],["Organic Search","Enterprise","2","0","0.0000"],["Organic Search","Strategic","1","0","0.0000"],["Paid Search","Trial","1","0","0.0000"],["Paid Social","SMB","2","0","0.0000"],["null","SMB","1","0","0.0000"]]

Problem

Task

A user is considered converted if they have at least one row in conversions with is_conversion = true within 30 days of their signup date.

Requirements

Return one row per channel and segment.
Count total signed-up users and converted users.
Compute conversion rate as converted_users / total_users, rounded to 4 decimal places.
Exclude users whose segment is NULL.
Order results by conversion rate descending, then by channel and segment ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user signed up
channel	VARCHAR(50)	Acquisition channel
segment	VARCHAR(50)	Customer segment

`conversions`

column	type	description
conversion_id	INT	Unique conversion event identifier
user_id	INT	User tied to the conversion event
conversion_date	DATE	Date of conversion event
is_conversion	BOOLEAN	Whether the event counts as a conversion

`channels`

column	type	description
channel	VARCHAR(50)	Channel name
channel_group	VARCHAR(50)	Higher-level channel grouping

Sample Data

`users`

user_id	signup_date	channel	segment
1	2024-01-05	Paid Search	SMB
2	2024-01-07	Organic Search	Enterprise
3	2024-01-10	Paid Social	SMB
4	2024-01-12	Email	Mid-Market

`conversions`

conversion_id	user_id	conversion_date	is_conversion
101	1	2024-01-20	true
102	1	2024-02-02	true
103	2	2024-02-15	true
104	3	2024-01-25	false

Expected Output

channel	segment	total_users	converted_users	conversion_rate
Email	Mid-Market	2	2	1.0000
Organic Search	SMB	1	1	1.0000
Paid Search	SMB	2	1	0.5000
Paid Social	SMB	2	1	0.5000
Organic Search	Enterprise	2	0	0.0000

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
channel	VARCHAR(50)	Acquisition channel for the signup
segment	VARCHAR(50)	Customer segment

conversions

Column	Type	Description
conversion_idPK	INT	Unique conversion event identifier
user_id	INT	User associated with the event
conversion_date	DATE	Date the conversion event occurred
is_conversion	BOOLEAN	Whether the event should count as a conversion

channels

Column	Type	Description
channelPK	VARCHAR(50)	Channel name
channel_group	VARCHAR(50)	Higher-level grouping of the channel

Sample Data

Example 1

Input

{"users":[[1,"2024-01-05","Paid Search","SMB"],[2,"2024-01-07","Organic Search","Enterprise"],[3,"2024-01-10","Paid Social","SMB"],[4,"2024-01-12","Email","Mid-Market"],[5,"2024-01-15","Paid Search","SMB"],[6,"2024-01-18","Organic Search","SMB"],[7,"2024-01-20","Email","Mid-Market"],[8,"2024-01-22","Paid Social","SMB"],[9,"2024-01-25","Organic Search","Enterprise"],[10,"2024-01-28","Referral",null],[11,"2023-12-30","Paid Search","Enterprise"],[12,"2024-01-09",null,"SMB"],["1","2024-01-02","Organ

Output

[["Email","Mid-Market","2","2","1.0000"],["Organic Search","SMB","1","1","1.0000"],["Paid Search","SMB","2","1","0.5000"],["Email","Developer","1","0","0.0000"],["Organic Search","Enterprise","2","0","0.0000"],["Organic Search","Strategic","1","0","0.0000"],["Paid Search","Trial","1","0","0.0000"],["Paid Social","SMB","2","0","0.0000"],["null","SMB","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