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 |
