Measure elapsed time within a session
Your task
Return session_key, event_count, and duration_seconds for every session. Use zero seconds for one-event sessions. Sort by session key.
Here, session duration is the elapsed time between its first and last logged events.
Sample data
product_events| id | customer_id | session_key | event_at | event_name |
|---|---|---|---|---|
| 301 | 1 | a-1 | 2025-01-02 09:00:00 | search |
| 302 | 1 | a-1 | 2025-01-02 09:01:00 | view_product |
| 303 | 1 | a-1 | 2025-01-02 09:02:00 | add_to_cart |
| 304 | 1 | a-1 | 2025-01-02 09:04:00 | purchase |
| 305 | 2 | c-1 | 2025-01-05 10:00:00 | view_product |
| 306 | 2 | c-1 | 2025-01-05 10:01:00 | purchase |
| 307 | 2 | c-1 | 2025-01-05 10:02:00 | add_to_cart |
| 308 | 3 | j-1 | 2025-01-12 11:00:00 | search |
| 309 | 3 | j-1 | 2025-01-12 11:02:00 | view_product |
| 310 | 4 | m-1 | 2025-02-01 08:00:00 | view_product |
Preview of 10 of 25 rows
Expected output
| session_key | event_count | duration_seconds |
|---|---|---|
| a-1 | 4 | 240 |
| a-2 | 2 | 60 |
| c-1 | 3 | 120 |
| c-2 | 1 | 0 |
| d-1 | 1 | 0 |
| d-2 | 2 | 120 |
| j-1 | 2 | 120 |
| m-1 | 3 | 60 |
| m-2 | 2 | 180 |
| s-1 | 3 | 300 |
| t-1 | 2 | 120 |
Show a hint
Subtract UNIXEPOCH of the earliest event from UNIXEPOCH of the latest event.
Product events and funnels guide
Choose the event, session, or customer grain
An event is one logged action. A session is a group of events with the same session_key. A customer can have several sessions. Choose the grain before counting so a busy visitor cannot accidentally count as many people.
SELECT event_name, COUNT(*) AS events, COUNT(DISTINCT session_key) AS sessions
FROM product_events
GROUP BY event_name
ORDER BY event_name;Order events before evaluating a funnel
A funnel measures steps completed in a defined order. Events in the same session can share an exact timestamp, so use the event ID as a tie-breaker. A session with a purchase before the cart event does not meet a view, cart, purchase funnel.
SELECT session_key, event_name,
ROW_NUMBER() OVER (PARTITION BY session_key ORDER BY event_at, id) AS position
FROM product_events
ORDER BY session_key, position;Worked solution
SELECT session_key,COUNT(*) AS event_count,UNIXEPOCH(MAX(event_at))-UNIXEPOCH(MIN(event_at)) AS duration_seconds FROM product_events GROUP BY session_key ORDER BY session_key;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
