Compare Chime Channel Conversion Rates

MediumSQL · PostgreSQL

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

Tablesusersacquisition_touchesconversion_events