Interview Guides

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

Compare Chime Channel Conversion Rates

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsGroup ByCase When

Also asked at

Problem

Task

Chime’s growth team wants to compare signup-to-funded-account conversion rates across acquisition channels for users acquired in January 2024. Write a SQL query to measure channel performance.

Requirements

Consider only users whose signup_date falls between 2024-01-01 and 2024-01-31.
Attribute each user to their acquisition channel from acquisition_touches.
Count a user as converted only if they have a funded event in conversion_events within 14 days after signup.
Return, for each channel, the number of signed-up users, number of converted users, and conversion rate as a percentage rounded to 2 decimals.
Include channels with zero conversions, and sort by conversion rate descending, then by channel name.

Table Definitions

`users`

column	type	description
user_id	INT	Unique Chime user identifier
signup_date	DATE	Date the user signed up
app_surface	VARCHAR(50)	Chime surface where signup started

`acquisition_touches`

column	type	description
touch_id	INT	Unique acquisition touch identifier
user_id	INT	User tied to the acquisition touch
channel	VARCHAR(50)	Acquisition channel
campaign_name	VARCHAR(100)	Campaign or partner name

`conversion_events`

column	type	description
event_id	INT	Unique conversion event identifier
user_id	INT	User tied to the event
event_type	VARCHAR(30)	Event type such as funded or card_activated
event_date	DATE	Date the event occurred

Sample Data

`users`

user_id	signup_date	app_surface
3	2024-01-20	Referral Landing Page
1	2024-01-03	Chime App
9	2024-01-10	Chime App
6	2024-01-28	Chime App
2	2024-01-05	Chime.com
11	2024-01-31	Chime.com
5	2024-01-25	Chime App
8	2024-01-08	Chime.com
4	2024-02-01	Chime App
7	2024-01-15	Referral Landing Page
10	2024-01-18	Chime App
12	2024-01-12	Chime App

`acquisition_touches`

touch_id	user_id	channel	campaign_name
104	4	Paid Search	Brand SEM
101	1	Paid Search	New Year SEM
108	8	Organic Search	SEO Blog
111	11	Referral	Member Get Member
103	3	Referral	Member Get Member
106	6	Paid Social	TikTok Prospecting
109	9	Paid Search	Brand SEM
102	2	Paid Social	Meta Lookalike
112	12		Unknown Attribution
107	7	Affiliate	Coupon Partner
105	5	Organic Search	SEO Blog
110	10	Paid Social	Meta Retargeting

`conversion_events`

event_id	user_id	event_type	event_date
205	5	funded	2024-02-10
201	1	funded	2024-01-10
210	10	card_activated	2024-01-25
208	8	funded	2024-01-15
204	3	funded	2024-02-05
212	12	funded	2024-01-20
206	6	funded	2024-02-12
202	2	funded	2024-01-25
209	9	funded	2024-01-12
203	2	card_activated	2024-01-09
207	7	funded	2024-01-20
211	99	funded	2024-01-11

Expected Output

channel	signed_up_users	converted_users	conversion_rate_pct
Organic Search	2	1	50.00
Paid Search	2	2	100.00
Paid Social	3	0	0.00
Referral	2	1	50.00
Affiliate	1	1	100.00
Unattributed	1	1	100.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique Chime user identifier
signup_date	DATE	Date the user signed up
app_surface	VARCHAR(50)	Chime surface where signup started

acquisition_touches

Column	Type	Description
touch_idPK	INT	Unique acquisition touch identifier
user_id	INT	User tied to the acquisition touch
channel	VARCHAR(50)	Acquisition channel
campaign_name	VARCHAR(100)	Campaign or partner name

conversion_events

Column	Type	Description
event_idPK	INT	Unique conversion event identifier
user_id	INT	User tied to the event
event_type	VARCHAR(30)	Event type such as funded or card_activated
event_date	DATE	Date the event occurred

Sample Data

Example 1

Input

{"users":[["3","2024-01-20","Referral Landing Page"],["1","2024-01-03","Chime App"],["9","2024-01-10","Chime App"],["6","2024-01-28","Chime App"],["2","2024-01-05","Chime.com"],["11","2024-01-31","Chime.com"],["5","2024-01-25","Chime App"],["8","2024-01-08","Chime.com"],["4","2024-02-01","Chime App"],["7","2024-01-15","Referral Landing Page"],["10","2024-01-18","Chime App"],["12","2024-01-12","Chime App"],["13","2024-01-17","Chime.com"],["14","2024-01-02","Member Invite Flow"],["15","2024-01-08"

Output

[["Affiliate","1","1","100.00"],["Display","1","1","100.00"],["Paid Search","3","3","100.00"],["App Store Search","2","1","50.00"],["Organic Search","2","1","50.00"],["PR","2","1","50.00"],["Paid Social","4","1","25.00"],["Unattributed","13","1","7.69"],["Influencer","1","0","0.00"],["Non-Brand Search","2","0","0.00"],["Podcast","1","0","0.00"],["Reddit","1","0","0.00"],["Referral","2","0","0.00"],["TikTok Organic","1","0","0.00"]]

Problem

Task

Chime’s growth team wants to compare signup-to-funded-account conversion rates across acquisition channels for users acquired in January 2024. Write a SQL query to measure channel performance.

Requirements

Consider only users whose signup_date falls between 2024-01-01 and 2024-01-31.
Attribute each user to their acquisition channel from acquisition_touches.
Count a user as converted only if they have a funded event in conversion_events within 14 days after signup.
Return, for each channel, the number of signed-up users, number of converted users, and conversion rate as a percentage rounded to 2 decimals.
Include channels with zero conversions, and sort by conversion rate descending, then by channel name.

Table Definitions

`users`

column	type	description
user_id	INT	Unique Chime user identifier
signup_date	DATE	Date the user signed up
app_surface	VARCHAR(50)	Chime surface where signup started

`acquisition_touches`

column	type	description
touch_id	INT	Unique acquisition touch identifier
user_id	INT	User tied to the acquisition touch
channel	VARCHAR(50)	Acquisition channel
campaign_name	VARCHAR(100)	Campaign or partner name

`conversion_events`

column	type	description
event_id	INT	Unique conversion event identifier
user_id	INT	User tied to the event
event_type	VARCHAR(30)	Event type such as funded or card_activated
event_date	DATE	Date the event occurred

Sample Data

`users`

user_id	signup_date	app_surface
3	2024-01-20	Referral Landing Page
1	2024-01-03	Chime App
9	2024-01-10	Chime App
6	2024-01-28	Chime App
2	2024-01-05	Chime.com
11	2024-01-31	Chime.com
5	2024-01-25	Chime App
8	2024-01-08	Chime.com
4	2024-02-01	Chime App
7	2024-01-15	Referral Landing Page
10	2024-01-18	Chime App
12	2024-01-12	Chime App

`acquisition_touches`

touch_id	user_id	channel	campaign_name
104	4	Paid Search	Brand SEM
101	1	Paid Search	New Year SEM
108	8	Organic Search	SEO Blog
111	11	Referral	Member Get Member
103	3	Referral	Member Get Member
106	6	Paid Social	TikTok Prospecting
109	9	Paid Search	Brand SEM
102	2	Paid Social	Meta Lookalike
112	12		Unknown Attribution
107	7	Affiliate	Coupon Partner
105	5	Organic Search	SEO Blog
110	10	Paid Social	Meta Retargeting

`conversion_events`

event_id	user_id	event_type	event_date
205	5	funded	2024-02-10
201	1	funded	2024-01-10
210	10	card_activated	2024-01-25
208	8	funded	2024-01-15
204	3	funded	2024-02-05
212	12	funded	2024-01-20
206	6	funded	2024-02-12
202	2	funded	2024-01-25
209	9	funded	2024-01-12
203	2	card_activated	2024-01-09
207	7	funded	2024-01-20
211	99	funded	2024-01-11

Expected Output

channel	signed_up_users	converted_users	conversion_rate_pct
Organic Search	2	1	50.00
Paid Search	2	2	100.00
Paid Social	3	0	0.00
Referral	2	1	50.00
Affiliate	1	1	100.00
Unattributed	1	1	100.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique Chime user identifier
signup_date	DATE	Date the user signed up
app_surface	VARCHAR(50)	Chime surface where signup started

acquisition_touches

Column	Type	Description
touch_idPK	INT	Unique acquisition touch identifier
user_id	INT	User tied to the acquisition touch
channel	VARCHAR(50)	Acquisition channel
campaign_name	VARCHAR(100)	Campaign or partner name

conversion_events

Column	Type	Description
event_idPK	INT	Unique conversion event identifier
user_id	INT	User tied to the event
event_type	VARCHAR(30)	Event type such as funded or card_activated
event_date	DATE	Date the event occurred

Sample Data

Example 1

Input

{"users":[["3","2024-01-20","Referral Landing Page"],["1","2024-01-03","Chime App"],["9","2024-01-10","Chime App"],["6","2024-01-28","Chime App"],["2","2024-01-05","Chime.com"],["11","2024-01-31","Chime.com"],["5","2024-01-25","Chime App"],["8","2024-01-08","Chime.com"],["4","2024-02-01","Chime App"],["7","2024-01-15","Referral Landing Page"],["10","2024-01-18","Chime App"],["12","2024-01-12","Chime App"],["13","2024-01-17","Chime.com"],["14","2024-01-02","Member Invite Flow"],["15","2024-01-08"

Output

[["Affiliate","1","1","100.00"],["Display","1","1","100.00"],["Paid Search","3","3","100.00"],["App Store Search","2","1","50.00"],["Organic Search","2","1","50.00"],["PR","2","1","50.00"],["Paid Social","4","1","25.00"],["Unattributed","13","1","7.69"],["Influencer","1","0","0.00"],["Non-Brand Search","2","0","0.00"],["Podcast","1","0","0.00"],["Reddit","1","0","0.00"],["Referral","2","0","0.00"],["TikTok Organic","1","0","0.00"]]

Practice SQL

PostgreSQL

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

Up next

Compare Conversion Rates by ChannelEasy

Conversion Rate by Acquisition ChannelMedium

Landing Page to Sign-Up ConversionEasy

Next question

Compare Chime Channel Conversion Rates

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsGroup ByCase When

Also asked at

Problem

Task

Chime’s growth team wants to compare signup-to-funded-account conversion rates across acquisition channels for users acquired in January 2024. Write a SQL query to measure channel performance.

Requirements

Consider only users whose signup_date falls between 2024-01-01 and 2024-01-31.
Attribute each user to their acquisition channel from acquisition_touches.
Count a user as converted only if they have a funded event in conversion_events within 14 days after signup.
Return, for each channel, the number of signed-up users, number of converted users, and conversion rate as a percentage rounded to 2 decimals.
Include channels with zero conversions, and sort by conversion rate descending, then by channel name.

Table Definitions

`users`

column	type	description
user_id	INT	Unique Chime user identifier
signup_date	DATE	Date the user signed up
app_surface	VARCHAR(50)	Chime surface where signup started

`acquisition_touches`

column	type	description
touch_id	INT	Unique acquisition touch identifier
user_id	INT	User tied to the acquisition touch
channel	VARCHAR(50)	Acquisition channel
campaign_name	VARCHAR(100)	Campaign or partner name

`conversion_events`

column	type	description
event_id	INT	Unique conversion event identifier
user_id	INT	User tied to the event
event_type	VARCHAR(30)	Event type such as funded or card_activated
event_date	DATE	Date the event occurred

Sample Data

`users`

user_id	signup_date	app_surface
3	2024-01-20	Referral Landing Page
1	2024-01-03	Chime App
9	2024-01-10	Chime App
6	2024-01-28	Chime App
2	2024-01-05	Chime.com
11	2024-01-31	Chime.com
5	2024-01-25	Chime App
8	2024-01-08	Chime.com
4	2024-02-01	Chime App
7	2024-01-15	Referral Landing Page
10	2024-01-18	Chime App
12	2024-01-12	Chime App

`acquisition_touches`

touch_id	user_id	channel	campaign_name
104	4	Paid Search	Brand SEM
101	1	Paid Search	New Year SEM
108	8	Organic Search	SEO Blog
111	11	Referral	Member Get Member
103	3	Referral	Member Get Member
106	6	Paid Social	TikTok Prospecting
109	9	Paid Search	Brand SEM
102	2	Paid Social	Meta Lookalike
112	12		Unknown Attribution
107	7	Affiliate	Coupon Partner
105	5	Organic Search	SEO Blog
110	10	Paid Social	Meta Retargeting

`conversion_events`

event_id	user_id	event_type	event_date
205	5	funded	2024-02-10
201	1	funded	2024-01-10
210	10	card_activated	2024-01-25
208	8	funded	2024-01-15
204	3	funded	2024-02-05
212	12	funded	2024-01-20
206	6	funded	2024-02-12
202	2	funded	2024-01-25
209	9	funded	2024-01-12
203	2	card_activated	2024-01-09
207	7	funded	2024-01-20
211	99	funded	2024-01-11

Expected Output

channel	signed_up_users	converted_users	conversion_rate_pct
Organic Search	2	1	50.00
Paid Search	2	2	100.00
Paid Social	3	0	0.00
Referral	2	1	50.00
Affiliate	1	1	100.00
Unattributed	1	1	100.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique Chime user identifier
signup_date	DATE	Date the user signed up
app_surface	VARCHAR(50)	Chime surface where signup started

acquisition_touches

Column	Type	Description
touch_idPK	INT	Unique acquisition touch identifier
user_id	INT	User tied to the acquisition touch
channel	VARCHAR(50)	Acquisition channel
campaign_name	VARCHAR(100)	Campaign or partner name

conversion_events

Column	Type	Description
event_idPK	INT	Unique conversion event identifier
user_id	INT	User tied to the event
event_type	VARCHAR(30)	Event type such as funded or card_activated
event_date	DATE	Date the event occurred

Sample Data

Example 1

Input

{"users":[["3","2024-01-20","Referral Landing Page"],["1","2024-01-03","Chime App"],["9","2024-01-10","Chime App"],["6","2024-01-28","Chime App"],["2","2024-01-05","Chime.com"],["11","2024-01-31","Chime.com"],["5","2024-01-25","Chime App"],["8","2024-01-08","Chime.com"],["4","2024-02-01","Chime App"],["7","2024-01-15","Referral Landing Page"],["10","2024-01-18","Chime App"],["12","2024-01-12","Chime App"],["13","2024-01-17","Chime.com"],["14","2024-01-02","Member Invite Flow"],["15","2024-01-08"

Output

[["Affiliate","1","1","100.00"],["Display","1","1","100.00"],["Paid Search","3","3","100.00"],["App Store Search","2","1","50.00"],["Organic Search","2","1","50.00"],["PR","2","1","50.00"],["Paid Social","4","1","25.00"],["Unattributed","13","1","7.69"],["Influencer","1","0","0.00"],["Non-Brand Search","2","0","0.00"],["Podcast","1","0","0.00"],["Reddit","1","0","0.00"],["Referral","2","0","0.00"],["TikTok Organic","1","0","0.00"]]

Problem

Task

Chime’s growth team wants to compare signup-to-funded-account conversion rates across acquisition channels for users acquired in January 2024. Write a SQL query to measure channel performance.

Requirements

Consider only users whose signup_date falls between 2024-01-01 and 2024-01-31.
Attribute each user to their acquisition channel from acquisition_touches.
Count a user as converted only if they have a funded event in conversion_events within 14 days after signup.
Return, for each channel, the number of signed-up users, number of converted users, and conversion rate as a percentage rounded to 2 decimals.
Include channels with zero conversions, and sort by conversion rate descending, then by channel name.

Table Definitions

`users`

column	type	description
user_id	INT	Unique Chime user identifier
signup_date	DATE	Date the user signed up
app_surface	VARCHAR(50)	Chime surface where signup started

`acquisition_touches`

column	type	description
touch_id	INT	Unique acquisition touch identifier
user_id	INT	User tied to the acquisition touch
channel	VARCHAR(50)	Acquisition channel
campaign_name	VARCHAR(100)	Campaign or partner name

`conversion_events`

column	type	description
event_id	INT	Unique conversion event identifier
user_id	INT	User tied to the event
event_type	VARCHAR(30)	Event type such as funded or card_activated
event_date	DATE	Date the event occurred

Sample Data

`users`

user_id	signup_date	app_surface
3	2024-01-20	Referral Landing Page
1	2024-01-03	Chime App
9	2024-01-10	Chime App
6	2024-01-28	Chime App
2	2024-01-05	Chime.com
11	2024-01-31	Chime.com
5	2024-01-25	Chime App
8	2024-01-08	Chime.com
4	2024-02-01	Chime App
7	2024-01-15	Referral Landing Page
10	2024-01-18	Chime App
12	2024-01-12	Chime App

`acquisition_touches`

touch_id	user_id	channel	campaign_name
104	4	Paid Search	Brand SEM
101	1	Paid Search	New Year SEM
108	8	Organic Search	SEO Blog
111	11	Referral	Member Get Member
103	3	Referral	Member Get Member
106	6	Paid Social	TikTok Prospecting
109	9	Paid Search	Brand SEM
102	2	Paid Social	Meta Lookalike
112	12		Unknown Attribution
107	7	Affiliate	Coupon Partner
105	5	Organic Search	SEO Blog
110	10	Paid Social	Meta Retargeting

`conversion_events`

event_id	user_id	event_type	event_date
205	5	funded	2024-02-10
201	1	funded	2024-01-10
210	10	card_activated	2024-01-25
208	8	funded	2024-01-15
204	3	funded	2024-02-05
212	12	funded	2024-01-20
206	6	funded	2024-02-12
202	2	funded	2024-01-25
209	9	funded	2024-01-12
203	2	card_activated	2024-01-09
207	7	funded	2024-01-20
211	99	funded	2024-01-11

Expected Output

channel	signed_up_users	converted_users	conversion_rate_pct
Organic Search	2	1	50.00
Paid Search	2	2	100.00
Paid Social	3	0	0.00
Referral	2	1	50.00
Affiliate	1	1	100.00
Unattributed	1	1	100.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique Chime user identifier
signup_date	DATE	Date the user signed up
app_surface	VARCHAR(50)	Chime surface where signup started

acquisition_touches

Column	Type	Description
touch_idPK	INT	Unique acquisition touch identifier
user_id	INT	User tied to the acquisition touch
channel	VARCHAR(50)	Acquisition channel
campaign_name	VARCHAR(100)	Campaign or partner name

conversion_events

Column	Type	Description
event_idPK	INT	Unique conversion event identifier
user_id	INT	User tied to the event
event_type	VARCHAR(30)	Event type such as funded or card_activated
event_date	DATE	Date the event occurred

Sample Data

Example 1

Input

{"users":[["3","2024-01-20","Referral Landing Page"],["1","2024-01-03","Chime App"],["9","2024-01-10","Chime App"],["6","2024-01-28","Chime App"],["2","2024-01-05","Chime.com"],["11","2024-01-31","Chime.com"],["5","2024-01-25","Chime App"],["8","2024-01-08","Chime.com"],["4","2024-02-01","Chime App"],["7","2024-01-15","Referral Landing Page"],["10","2024-01-18","Chime App"],["12","2024-01-12","Chime App"],["13","2024-01-17","Chime.com"],["14","2024-01-02","Member Invite Flow"],["15","2024-01-08"

Output

[["Affiliate","1","1","100.00"],["Display","1","1","100.00"],["Paid Search","3","3","100.00"],["App Store Search","2","1","50.00"],["Organic Search","2","1","50.00"],["PR","2","1","50.00"],["Paid Social","4","1","25.00"],["Unattributed","13","1","7.69"],["Influencer","1","0","0.00"],["Non-Brand Search","2","0","0.00"],["Podcast","1","0","0.00"],["Reddit","1","0","0.00"],["Referral","2","0","0.00"],["TikTok Organic","1","0","0.00"]]

Practice SQL

PostgreSQL

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

Up next

Compare Conversion Rates by ChannelEasy

Conversion Rate by Acquisition ChannelMedium

Landing Page to Sign-Up ConversionEasy

Next question