Conversion Rate by Acquisition Channel | Dataford Interview Questions

Your question is Conversion Rate by Acquisition Channel. Start with the requirements and the two 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

BrightCart wants to measure how effectively each acquisition channel turns signups into paying customers. Write a SQL query to calculate the conversion rate by acquisition channel.

Requirements

Use users as the base table so channels with zero conversions are still included.
A user is considered converted if they have at least one row in subscriptions with status = 'active' or status = 'canceled'.
Return one row per acquisition channel with:
- acquisition_channel
- total_signups
- converted_users
- conversion_rate_pct rounded to 2 decimal places
Order the result by conversion_rate_pct descending, then acquisition_channel ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user
signup_date	DATE	Date the user signed up
country_code	VARCHAR(2)	User country code

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique subscription record identifier
user_id	INT	User associated with the subscription
start_date	DATE	Subscription start date
status	VARCHAR(20)	Subscription lifecycle status
monthly_amount	NUMERIC(10,2)	Monthly subscription amount