Rank Quora Acquisition Channels | Dataford Interview Questions

Your question is Rank Quora Acquisition Channels. 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

Quora’s growth team wants to compare acquisition quality across channels by looking at signup-to-conversion performance. Write a SQL query to find the top-performing acquisition channels by conversion rate.

Requirements

Use quora_users as the base table of acquired users.
Treat a user as converted if they have at least one row in user_conversions with conversion_type = 'activated' and a non-null conversion_date.
For each acquisition channel, return:
- acquisition_channel
- total acquired users
- converted users
- conversion rate as converted_users / total_users, rounded to 4 decimals
Exclude channels with fewer than 2 acquired users.
Return channels ranked from highest to lowest conversion rate. Break ties by higher converted user count, then alphabetically by channel name.

Schema

quora_users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up on Quora
acquisition_channel	VARCHAR(50)	Channel that acquired the user
country_code	VARCHAR(2)	Two-letter country code

user_conversions

Column	Type	Description
conversion_idPK	INT	Unique conversion event identifier
user_id	INT	User associated with the conversion event
conversion_type	VARCHAR(30)	Type of conversion event
conversion_date	DATE	Date the conversion occurred