Task
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_channeltotal_signupsconverted_usersconversion_rate_pct rounded to 2 decimal places
- Order the result by
conversion_rate_pct descending, then acquisition_channel 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 |
| country_code | VARCHAR(2) | User country |
subscriptions
| column | type | description |
|---|
| subscription_id | INT | Unique subscription record |
| user_id | INT | User tied to the subscription |
| start_date | DATE | Subscription start date |
| status | VARCHAR(20) | Subscription status |
| monthly_amount | NUMERIC(10,2) | Monthly subscription price |
Sample Data
users
| user_id | acquisition_channel | signup_date | country_code |
|---|
| 1 | Paid Search | 2024-01-05 | US |
| 2 | Organic Search | 2024-01-07 | CA |
| 3 | Referral | 2024-01-08 | US |
| 4 | Social | 2024-01-10 | GB |
| 5 | Email | 2024-01-11 | US |
subscriptions
| subscription_id | user_id | start_date | status | monthly_amount |
|---|
| 101 | 1 | 2024-01-06 | active | 29.99 |
| 102 | 2 | 2024-01-09 | trial | 0.00 |
| 103 | 3 | 2024-01-12 | canceled | 19.99 |
| 104 | 4 | 2024-01-15 | expired | 9.99 |
| 105 | 5 | 2024-01-20 | active | 14.99 |
Expected Output
| acquisition_channel | total_signups | converted_users | conversion_rate_pct |
|---|
| Email | 2 | 2 | 100.00 |
| Paid Search | 2 | 1 | 50.00 |
| Referral | 2 | 1 | 50.00 |
| Organic Search | 2 | 0 | 0.00 |
| Social | 2 | 0 | 0.00 |
