Compare Chime Channel Conversion Rates | Dataford Interview Questions

Your question is Compare Chime Channel Conversion Rates. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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.

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