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.
users as the base table so channels with zero conversions are still included.subscriptions with status = 'active' or status = 'canceled'.acquisition_channeltotal_signupsconverted_usersconversion_rate_pct rounded to 2 decimal placesconversion_rate_pct descending, then acquisition_channel ascending.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 |
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 | 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 |
| acquisition_channel | total_signups | converted_users | conversion_rate_pct |
|---|---|---|---|
| 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 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| acquisition_channel | VARCHAR(50) | Marketing channel that acquired the user |
| signup_date | DATE | Date the user signed up |
| country_code | VARCHAR(2) | User country code |
| Column | Type | Description |
|---|---|---|
| subscription_idPK | INT | Unique subscription record identifier |
| user_id | INT | User associated with the subscription |
| start_date | DATE | Subscription start date |
| status | VARCHAR(20) | Subscription lifecycle status |
| monthly_amount | NUMERIC(10,2) | Monthly subscription amount |
{"users":[[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"],[6,"Paid Search","2024-01-12","US"],[7,"Organic Search","2024-01-13",null],[8,"Referral","2024-01-14","DE"],[9,"Social","2024-01-15","US"],[10,"Email","2024-01-16","FR"],["1","Instagram Ads","2024-01-04","GB"],["2","null","2024-01-15","IT"],["3","Referral","2024-01-18","IE"],["4","Display","2024-01-13","FR"],["5","MarkeOutput[["Email","2","2","100.00"],["Referral","2","1","50.00"],["SEO Blog","2","1","50.00"],["Paid Search","3","1","33.33"],["Influencer","1","0","0.00"],["Organic Search","3","0","0.00"],["Retargeting","1","0","0.00"],["Social","2","0","0.00"],["Webinar","1","0","0.00"]]