Task
NovaCart wants to measure how users move through a simple purchase funnel: visit → checkout → purchase. Write a SQL query to calculate funnel performance by visit date.
Your query should use the event stream and user data to report how many distinct users reached each stage on each day, along with conversion rates between stages.
Requirements
- For each
visit_date, count distinct users who had a visit, then a checkout, then a purchase on the same day.
- A user should count at a later stage only if they also reached the earlier stage that day.
- Return
visit_date, visitors, checkout_users, purchase_users, visit_to_checkout_rate, and checkout_to_purchase_rate.
- Include only users from the
web acquisition channel.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| signup_date | DATE | User signup date |
| acquisition_channel | VARCHAR(20) | Marketing channel for the user |
| country | VARCHAR(20) | User country |
events
| column | type | description |
|---|
| event_id | INT | Unique event identifier |
| user_id | INT | User who generated the event |
| event_name | VARCHAR(20) | Funnel event: visit, checkout, or purchase |
| event_time | TIMESTAMP | Event timestamp |
| session_id | VARCHAR(20) | Session identifier |
Sample Data
users
| user_id | signup_date | acquisition_channel | country |
|---|
| 1 | 2024-01-01 | web | US |
| 2 | 2024-01-01 | web | CA |
| 3 | 2024-01-02 | mobile | US |
| 4 | 2024-01-02 | web | UK |
| 5 | 2024-01-03 | web | US |
| 6 | 2024-01-03 | partner | DE |
| 7 | 2024-01-04 | web | FR |
| 8 | 2024-01-04 | web | NULL |
events
| event_id | user_id | event_name | event_time | session_id |
|---|
| 101 | 2 | visit | 2024-02-01 09:00:00 | s2a |
| 102 | 1 | checkout | 2024-02-01 09:10:00 | s1a |
| 103 | 1 | visit | 2024-02-01 09:00:00 | s1a |
| 104 | 1 | purchase | 2024-02-01 09:20:00 | s1a |
| 105 | 2 | checkout | 2024-02-01 09:30:00 | s2a |
| 106 | 3 | visit | 2024-02-01 10:00:00 | s3a |
| 107 | 4 | visit | 2024-02-01 11:00:00 | s4a |
| 108 | 4 | purchase | 2024-02-01 11:10:00 | s4a |
| 109 | 5 | checkout | 2024-02-01 12:00:00 | s5a |
| 110 | 7 | visit | 2024-02-02 08:00:00 | s7a |
| 111 | 7 | checkout | 2024-02-02 08:10:00 | s7a |
| 112 | 8 | visit | 2024-02-02 09:00:00 | s8a |
Expected Output
| visit_date | visitors | checkout_users | purchase_users | visit_to_checkout_rate | checkout_to_purchase_rate |
|---|
| 2024-02-01 | 3 | 2 | 1 | 0.6667 | 0.5000 |
| 2024-02-02 | 2 | 1 | 0 | 0.5000 | 0.0000 |
