Your question is Daily Session Funnel Conversion Rates. Start with the requirements and the one table on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
A retail analytics team wants to analyze user conversion through the product funnel on a daily basis. Each event in the clickstream_events table is associated with a session. Funnel steps are: product_view, add_to_cart, checkout_start, purchase.
Write a SQL query to compute, for each session start date:
product_viewadd_to_cartcheckout_startpurchaseproduct_view to add_to_cart (as a decimal, rounded to 1 decimal place)product_view to purchase (as a decimal, rounded to 1 decimal place)sessions_with_add_to_cart / sessions_with_product_view and sessions_with_purchase / sessions_with_product_view. If the denominator is zero, return 0.0.| column | type | description |
|---|---|---|
| event_id | BIGINT | Primary key |
| user_id | BIGINT | User identifier (not unique per session) |
| session_id | VARCHAR(64) | Session identifier; used to group events |
| event_ts | TIMESTAMP | Event timestamp in UTC |
| event_name | VARCHAR(50) | Event type (e.g., product_view, add_to_cart) |
| page_url | VARCHAR(500) | URL where the event occurred; nullable |
| Column | Type | Description |
|---|---|---|
| event_idPK | BIGINT | Primary key |
| user_id | BIGINT | User identifier (not unique per session) |
| session_id | VARCHAR(64) | Session identifier; used to group events into sessions |
| event_ts | TIMESTAMP | Event timestamp in UTC |
| event_name | VARCHAR(50) | Event type (e.g., product_view, add_to_cart) |
| page_url | VARCHAR(500) | URL where the event occurred; nullable depending on instrumentation |