Task
HelloFresh wants a simple view of signup conversion performance by acquisition channel in the US marketing funnel. Write a SQL query to calculate conversion rate by acquisition_channel from a single table.
Requirements
- Return one row per
acquisition_channel.
- Count total visitors and total converted visitors, where a visitor is considered converted when
converted_to_subscriber = TRUE.
- Calculate conversion rate as
converted_visitors / total_visitors, rounded to 4 decimal places.
- Exclude rows where
acquisition_channel is NULL.
- Order results by conversion rate descending, then by acquisition channel ascending.
Table Definition
Table: hellofresh_web_sessions
| column | type | description |
|---|
| session_id | INT | Unique session identifier |
| visitor_id | INT | Visitor identifier |
| acquisition_channel | VARCHAR(50) | Marketing acquisition source |
| session_date | DATE | Date of the visit |
| converted_to_subscriber | BOOLEAN | Whether the visitor subscribed in that session |
Sample Data
| session_id | visitor_id | acquisition_channel | session_date | converted_to_subscriber |
|---|
| 5 | 1005 | Paid Search | 2024-01-02 | false |
| 1 | 1001 | Paid Social | 2024-01-03 | true |
| 9 | 1009 | Referral | 2024-01-04 | true |
| 3 | 1003 | Organic Search | 2024-01-02 | false |
| 11 | 1011 | Email | 2024-01-06 | true |
| 2 | 1002 | Paid Social | 2024-01-01 | false |
| 8 | 1008 | Referral | 2024-01-03 | false |
| 6 | 1006 | Paid Search | 2024-01-05 | true |
| 4 | 1004 | Organic Search | 2024-01-04 | false |
| 10 | 1010 | NULL | 2024-01-05 | true |
Expected Output
| acquisition_channel | total_visitors | converted_visitors | conversion_rate |
|---|
| Email | 2 | 1 | 0.5000 |
| Referral | 2 | 1 | 0.5000 |
| Paid Search | 3 | 1 | 0.3333 |
| Paid Social | 3 | 1 | 0.3333 |
| Organic Search | 2 | 0 | 0.0000 |
