Task
Chime’s Growth team wants a quick read on how well the sign-up landing page converts visitors into completed account sign-ups. Write a SQL query to calculate the conversion rate using a single event table.
Requirements
- Use only events from the
signup_landing surface.
- Count distinct visitors who had a
landing_page_visit event as the denominator.
- Count distinct visitors who had a
completed_signup event as the numerator.
- Return the number of landing page visitors, the number of completed sign-ups, and the conversion rate as a percentage rounded to 2 decimal places.
Table Definition
growth_funnel_events
| column_name | type | description |
|---|
| event_id | INT | Unique event row identifier |
| user_id | INT | Visitor or member identifier |
| event_name | VARCHAR(50) | Name of the funnel event |
| surface_name | VARCHAR(50) | Chime product surface where the event happened |
| event_date | DATE | Date of the event |
| device_type | VARCHAR(20) | Device used for the event |
Sample Data
| event_id | user_id | event_name | surface_name | event_date | device_type |
|---|
| 3 | 102 | landing_page_visit | signup_landing | 2024-06-01 | android |
| 1 | 101 | landing_page_visit | signup_landing | 2024-06-01 | ios |
| 8 | 106 | completed_signup | signup_landing | 2024-06-02 | web |
| 5 | 103 | completed_signup | signup_landing | 2024-06-01 | web |
| 10 | 108 | landing_page_visit | home_feed | 2024-06-02 | ios |
| 2 | 101 | completed_signup | signup_landing | 2024-06-01 | ios |
| 6 | 104 | landing_page_visit | signup_landing | 2024-06-02 | ios |
| 9 | 107 | landing_page_visit | signup_landing | 2024-06-02 | null |
| 4 | 102 | landing_page_visit | signup_landing | 2024-06-01 | android |
| 7 | 105 | landing_page_visit | signup_landing | 2024-06-02 | web |
Expected Output
| landing_page_visitors | completed_signups | conversion_rate_pct |
|---|
| 6 | 3 | 50.00 |
