Context
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.
Task
Write a SQL query to compute, for each session start date:
- The number of sessions started
- The number of sessions with at least one
product_view
- The number of sessions with at least one
add_to_cart
- The number of sessions with at least one
checkout_start
- The number of sessions with at least one
purchase
- The conversion rate from
product_view to add_to_cart (as a decimal, rounded to 1 decimal place)
- The conversion rate from
product_view to purchase (as a decimal, rounded to 1 decimal place)
Requirements
- Each session is counted only once per funnel step, regardless of how many times the event occurs in the session.
- Conversion rates are calculated as:
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.
- Output one row per session start date, ordered by date ascending.
Table: clickstream_events
| 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 |
Sample Data
| event_id | user_id | session_id | event_ts | event_name | page_url |
|---|
| 1001 | 501 | s1 | 2026-02-10 10:00:00 | product_view | /p/sku-1 |
| 1002 | 501 | s1 | 2026-02-10 10:02:00 | add_to_cart | /cart |
| 1003 | 501 | s1 | 2026-02-10 10:05:00 | checkout_start | /checkout |
| 1004 | 501 | s1 | 2026-02-10 10:07:00 | purchase | /order/abc |
| 1005 | 777 | s2 | 2026-02-10 11:00:00 | product_view | /p/sku-9 |
| 1006 | 777 | s2 | 2026-02-10 11:10:00 | checkout_start | /checkout |
| 1007 | 888 | s3 | 2026-02-11 09:00:00 | add_to_cart | /cart |
| 1008 | 888 | s3 | 2026-02-11 09:01:00 | product_view | /p/sku-2 |
| 1009 | 888 | s3 | 2026-02-11 09:03:00 | add_to_cart | /cart |
| 1010 | 999 | s4 | 2026-02-12 12:00:00 | product_view | /p/sku-3 |
| 1011 | 999 | s4 | 2026-02-12 12:02:00 | add_to_cart | /cart |
Expected Output
| session_start_date | sessions_started | sessions_with_product_view | sessions_with_add_to_cart | sessions_with_checkout_start | sessions_with_purchase | add_to_cart_rate | purchase_rate |
|---|
| 2026-02-10 | 2 | 2 | 1 | 1 | 1 | 0.5 | 0.5 |
| 2026-02-11 | 1 | 1 | 1 | 0 | 0 | 1.0 | 0.0 |
| 2026-02-12 | 1 | 1 | 1 | 0 | 0 | 1.0 | 0.0 |
