Task
BrightCart wants to measure how well each acquisition channel converts visitors into paying customers. Write a SQL query to calculate the conversion rate by acquisition channel.
Requirements
- Count total acquired users per channel from the
users table.
- Count converted users per channel, where a user is considered converted if they have at least one
completed order.
- Return
acquisition_channel, total_users, converted_users, and conversion_rate_pct rounded to 2 decimal places.
- Include channels with zero conversions, and sort by conversion rate descending, then channel name ascending.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| acquisition_channel | VARCHAR(50) | Marketing channel that acquired the user |
| signup_date | DATE | User signup date |
orders
| column | type | description |
|---|
| order_id | INT | Unique order identifier |
| user_id | INT | User who placed the order |
| order_date | DATE | Order date |
| order_status | VARCHAR(20) | Order status such as completed or cancelled |
| order_amount | DECIMAL(10,2) | Order value |
Sample Data
users
| user_id | acquisition_channel | signup_date |
|---|
| 3 | Referral | 2024-01-12 |
| 1 | Paid Search | 2024-01-05 |
| 8 | Email | 2024-02-10 |
| 6 | Organic Search | 2024-02-01 |
| 2 | Paid Search | 2024-01-08 |
| 10 | NULL | 2024-02-20 |
| 5 | Social | 2024-01-25 |
| 4 | Social | 2024-01-20 |
| 7 | Organic Search | 2024-02-05 |
| 9 | Affiliate | 2024-02-15 |
| 11 | Email | 2024-02-22 |
| 12 | Referral | 2024-02-25 |
orders
| order_id | user_id | order_date | order_status | order_amount |
|---|
| 105 | 5 | 2024-02-03 | pending | 40.00 |
| 101 | 1 | 2024-01-10 | completed | 120.00 |
| 109 | 9 | 2024-02-18 | completed | 95.00 |
| 103 | 2 | 2024-01-18 | cancelled | 60.00 |
| 111 | 11 | 2024-02-25 | completed | 70.00 |
| 107 | 7 | 2024-02-09 | completed | 85.00 |
| 112 | 12 | 2024-02-28 | refunded | 55.00 |
| 102 | 1 | 2024-01-15 | completed | 80.00 |
| 106 | 6 | 2024-02-06 | completed | 150.00 |
| 104 | 4 | 2024-01-28 | completed | 200.00 |
| 108 | 8 | 2024-02-12 | cancelled | 30.00 |
| 110 | 99 | 2024-02-20 | completed | 110.00 |
Expected Output
| acquisition_channel | total_users | converted_users | conversion_rate_pct |
|---|
| Affiliate | 1 | 1 | 100.00 |
| Organic Search | 2 | 2 | 100.00 |
| Email | 2 | 1 | 50.00 |
| Paid Search | 2 | 1 | 50.00 |
| Social | 2 | 1 | 50.00 |
| Referral | 2 | 0 | 0.00 |
