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.
visit_date, count distinct users who had a visit, then a checkout, then a purchase on the same day.visit_date, visitors, checkout_users, purchase_users, visit_to_checkout_rate, and checkout_to_purchase_rate.web acquisition channel.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 |
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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| signup_date | DATE | Date the user signed up |
| acquisition_channel | VARCHAR(20) | Channel that acquired the user |
| country | VARCHAR(20) | User country |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| user_id | INT | User who generated the event |
| event_name | VARCHAR(20) | Event type in the funnel |
| event_time | TIMESTAMP | Timestamp of the event |
| session_id | VARCHAR(20) | Session identifier |
| 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 | |
| 9 | 2024-01-05 | web | US |
| 10 | 2024-01-05 | mobile | IN |
| 1 | 2024-01-04 | retargeting | ZA |
| 2 | 2023-12-30 | seo | IN |
| 3 | 2024-01-08 | event | FR |
| 4 | 2024-01-08 | retargeting | PT |
| 5 | 2024-01-05 | mobile | DE |
| 6 | 2024-01-04 | retargeting | NO |
| 7 | 2024-01-06 | social | FI |
| 8 | 2024-01-02 | retargeting | NO |
| 9 | 2024-01-02 | retargeting | US |
| 10 | 2024-01-02 | affiliate | NO |
| 11 | 2024-01-01 | web | ZA |
| 12 | 2024-01-08 | sms | PT |
| 13 | 2023-12-29 | event | ZA |
| 14 | 2024-01-06 | play_store | AT |
| 15 | 2023-12-29 | display | FR |
| 16 | 2024-01-02 | FR |
| 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 |
| 113 | 8 | visit | 2024-02-02 09:05:00 | s8a |
| 114 | 9 | purchase | 2024-02-02 10:00:00 | s9a |
| 115 | 6 | visit | 2024-02-02 10:30:00 | s6a |
| 116 | visit | 2024-02-02 11:00:00 | snull | |
| 117 | 2 | purchase | 2024-02-02 12:00:00 | s2b |
| 118 | 10 | visit | 2024-02-02 13:00:00 | s10a |
| 1 | 41 | shipping_selected | 2024-02-02 20:04:59 | sess_web_01 |
| 2 | 22 | payment_success | 2024-02-03 23:32:09 | s2a |
| 3 | 20 | logout | 2024-02-02 20:20:47 | s14a |
| 4 | 62 | shipping_selected | 2024-02-01 14:09:33 | s2b |
| 5 | 63 | shipping_selected | 2024-01-29 16:37:04 | s12a |
| 6 | 75 | logout | 2024-02-01 18:44:39 | s20a |
| 7 | 51 | logout | 2024-02-05 09:53:17 | s9a |
| 8 | 67 | review_submitted | 2024-02-05 11:22:52 | sess_chk_01 |
| 9 | 43 | support_contact | 2024-02-05 07:36:56 | snull |
| 10 | 29 | refund_requested | 2024-01-31 22:05:10 | s13a |
| 11 | 92 | address_added | 2024-01-30 07:41:50 | s6a |
| 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 |
