Task
Rippling’s growth team wants to compare signup conversion performance across acquisition channels for a recent campaign. Write a SQL query to calculate conversion rates by channel using a single event table.
Requirements
- For each
channel, count how many users had a visit event.
- Count how many users had a
signup event.
- Return
channel, visits, signups, and conversion_rate where conversion_rate = signups / visits.
- Exclude rows where
channel is NULL.
- Order results by
conversion_rate descending, then channel ascending.
Table Definition
rippling_growth_events
| column | type | description |
|---|
| event_id | INT | Unique event row ID |
| user_id | INT | User identifier |
| channel | VARCHAR(50) | Acquisition channel tied to the event |
| event_type | VARCHAR(20) | Event type such as visit or signup |
| event_date | DATE | Date of the event |
Sample Data
| event_id | user_id | channel | event_type | event_date |
|---|
| 1 | 101 | Paid Search | visit | 2024-06-03 |
| 2 | 102 | Organic Search | visit | 2024-06-01 |
| 3 | 101 | Paid Search | signup | 2024-06-04 |
| 4 | 103 | Partner Referral | visit | 2024-06-02 |
| 5 | 104 | Paid Social | visit | 2024-06-01 |
| 6 | 104 | Paid Social | signup | 2024-06-02 |
| 7 | 105 | Paid Social | visit | 2024-06-03 |
| 8 | 106 | Organic Search | signup | 2024-06-05 |
Expected Output
| channel | visits | signups | conversion_rate |
|---|
| Paid Search | 2 | 1 | 0.5000 |
| Paid Social | 3 | 1 | 0.3333 |
| Partner Referral | 2 | 0 | 0.0000 |
| Organic Search | 2 | 0 | 0.0000 |
