Welcome to the SQL screen.
The question is on your right: Daily Session Funnel Conversion Rates. Read through the requirements and the one table first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
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 |