Task
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.
Table Definitions
quora_users
| column | type | description |
|---|
| user_id | INT | Unique user ID |
| signup_date | DATE | Date the user signed up |
| acquisition_channel | VARCHAR(50) | Marketing or referral source |
| country_code | VARCHAR(2) | User country |
user_conversions
| column | type | description |
|---|
| conversion_id | INT | Unique conversion event ID |
| user_id | INT | User tied to the event |
| conversion_type | VARCHAR(30) | Type of conversion event |
| conversion_date | DATE | Date of conversion |
Sample Data
quora_users
| user_id | signup_date | acquisition_channel | country_code |
|---|
| 8 | 2024-01-08 | SEO | IN |
| 3 | 2024-01-03 | Paid Search | IN |
| 1 | 2024-01-01 | SEO | US |
| 10 | 2024-01-10 | Referral | GB |
| 6 | 2024-01-06 | Referral | CA |
| 4 | 2024-01-04 | Paid Search | US |
| 2 | 2024-01-02 | Quora Digest | US |
| 9 | 2024-01-09 | Quora Digest | US |
| 5 | 2024-01-05 | Quora Digest | NULL |
| 7 | 2024-01-07 | Influencer | US |
| 11 | 2024-01-11 | Influencer | IN |
| 12 | 2024-01-12 | Campus Ambassador | US |
user_conversions
| conversion_id | user_id | conversion_type | conversion_date |
|---|
| 101 | 1 | activated | 2024-01-02 |
| 102 | 1 | purchase | 2024-01-05 |
| 103 | 2 | activated | 2024-01-03 |
| 104 | 3 | activated | 2024-01-08 |
| 105 | 3 | activated | 2024-01-09 |
| 106 | 4 | email_verified | 2024-01-04 |
| 107 | 5 | activated | NULL |
| 108 | 6 | activated | 2024-01-10 |
| 109 | 8 | activated | 2024-01-12 |
| 110 | 9 | purchase | 2024-01-15 |
| 111 | 20 | activated | 2024-01-20 |
| 112 | 11 | activated | 2024-01-14 |
Expected Output
| acquisition_channel | total_users | converted_users | conversion_rate |
|---|
| SEO | 2 | 2 | 1.0000 |
| Influencer | 2 | 1 | 0.5000 |
| Paid Search | 2 | 1 | 0.5000 |
| Quora Digest | 3 | 1 | 0.3333 |
| Referral | 2 | 1 | 0.5000 |
