documentation/cookbook/sql/time-series/session-windows.md
Calculate sessions and elapsed time by identifying when state changes occur in time-series data. This "flip-flop" or "session" pattern is useful for analyzing user sessions, vehicle rides, machine operating cycles, or any scenario where you need to track duration between state transitions.
You have a table tracking vehicle lock status over time and want to calculate ride duration. A ride starts when lock_status changes from true (locked) to false (unlocked), and ends when it changes back to true.
Table schema:
CREATE TABLE vehicle_events (
vehicle_id SYMBOL,
lock_status BOOLEAN,
timestamp TIMESTAMP
) TIMESTAMP(timestamp) PARTITION BY DAY;
Sample data:
| timestamp | vehicle_id | lock_status |
|---|---|---|
| 10:00:00 | V001 | true |
| 10:05:00 | V001 | false |
| 10:25:00 | V001 | true |
| 10:30:00 | V001 | false |
| 10:45:00 | V001 | true |
You want to calculate the duration of each ride.
Use window functions to detect state changes, assign session IDs, then calculate durations:
WITH prevEvents AS (
SELECT *,
lag(lock_status::int) -- lag doesn't support booleans, so we convert to 1 or 0
OVER (PARTITION BY vehicle_id ORDER BY timestamp) as prev_status
FROM vehicle_events
WHERE timestamp IN '$today'
),
ride_sessions AS (
SELECT *,
SUM(CASE
WHEN lock_status = true AND prev_status = 0 THEN 1
WHEN lock_status = false AND prev_status = 1 THEN 1
ELSE 0
END) OVER (PARTITION BY vehicle_id ORDER BY timestamp) as ride
FROM prevEvents
),
global_sessions AS (
SELECT *, concat(vehicle_id, '#', ride) as session
FROM ride_sessions
),
totals AS (
SELECT
first(timestamp) as ts,
session,
FIRST(lock_status) as lock_status,
first(vehicle_id) as vehicle_id
FROM global_sessions
GROUP BY session
),
prev_ts AS (
SELECT *,
lag(timestamp) OVER (PARTITION BY vehicle_id ORDER BY timestamp) as prev_ts
FROM totals
)
SELECT
timestamp as ride_end,
vehicle_id,
datediff('s', prev_ts, timestamp) as duration_seconds
FROM prev_ts
WHERE lock_status = false AND prev_ts IS NOT NULL;
Results:
| ride_end | vehicle_id | duration_seconds |
|---|---|---|
| 10:25:00 | V001 | 1200 |
| 10:45:00 | V001 | 900 |
The query uses a five-step approach:
prevEvents)lag(lock_status::int) OVER (PARTITION BY vehicle_id ORDER BY timestamp)
For each row, get the status from the previous row. Convert boolean to integer (0/1) since lag doesn't support boolean types directly.
ride_sessions)SUM(CASE WHEN lock_status != prev_status THEN 1 ELSE 0 END)
OVER (PARTITION BY vehicle_id ORDER BY timestamp)
Whenever status changes, increment a counter. This creates sequential session IDs for each vehicle:
global_sessions)concat(vehicle_id, '#', ride)
Combine vehicle_id with ride number to create unique session identifiers across all vehicles.
totals)SELECT first(timestamp) as ts, ...
FROM global_sessions
GROUP BY session
For each session, get the timestamp and status at the beginning of that session.
prev_ts)lag(timestamp) OVER (PARTITION BY vehicle_id ORDER BY timestamp)
Get the timestamp from the previous session (for the same vehicle), then use datediff('s', prev_ts, timestamp) to calculate duration in seconds.
WHERE lock_status = false
Only show sessions where status is false (unlocked), which represents completed rides. The duration is from the previous session end (lock) to this session start (unlock).
Calculate total ride duration per vehicle per month:
WITH prevEvents AS (
SELECT *,
lag(lock_status::int) -- lag doesn't support booleans, so we convert to 1 or 0
OVER (PARTITION BY vehicle_id ORDER BY timestamp) as prev_status
FROM vehicle_events
WHERE timestamp IN '$now - 3M..$now'
),
ride_sessions AS (
SELECT *,
SUM(CASE
WHEN lock_status = true AND prev_status = 0 THEN 1
WHEN lock_status = false AND prev_status = 1 THEN 1
ELSE 0
END) OVER (PARTITION BY vehicle_id ORDER BY timestamp) as ride
FROM prevEvents
),
global_sessions AS (
SELECT *, concat(vehicle_id, '#', ride) as session
FROM ride_sessions
),
totals AS (
SELECT
first(timestamp) as ts,
session,
FIRST(lock_status) as lock_status,
first(vehicle_id) as vehicle_id
FROM global_sessions
GROUP BY session
),
prev_ts AS (
SELECT *,
lag(timestamp) OVER (PARTITION BY vehicle_id ORDER BY timestamp) as prev_ts
FROM totals
)
SELECT
timestamp_floor('M', timestamp) as month,
vehicle_id,
SUM(datediff('s', prev_ts, timestamp)) as total_ride_duration_seconds,
COUNT(*) as ride_count
FROM prev_ts
WHERE lock_status = false AND prev_ts IS NOT NULL
GROUP BY month, vehicle_id
ORDER BY month, vehicle_id;
User website sessions (1 hour timeout):
WITH prevEvents AS (
SELECT *,
lag(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) as prev_ts
FROM page_views
),
sessions AS (
SELECT *,
SUM(CASE
WHEN datediff('h', prev_ts, timestamp) > 1 THEN 1
ELSE 0
END) OVER (PARTITION BY user_id ORDER BY timestamp) as session_id
FROM prevEvents
)
SELECT
user_id,
session_id,
min(timestamp) as session_start,
max(timestamp) as session_end,
datediff('s', min(timestamp), max(timestamp)) as session_duration_seconds,
count(*) as page_views
FROM sessions
GROUP BY user_id, session_id;
Machine operating cycles:
-- When machine changes from 'off' to 'running' to 'off'
WITH prevStatus AS (
SELECT *,
lag(status) OVER (PARTITION BY machine_id ORDER BY timestamp) as prev_status
FROM machine_status
),
cycles AS (
SELECT *,
SUM(CASE
WHEN status != prev_status THEN 1
ELSE 0
END) OVER (PARTITION BY machine_id ORDER BY timestamp) as cycle
FROM prevStatus
)
SELECT
machine_id,
cycle,
min(timestamp) as cycle_start,
max(timestamp) as cycle_end
FROM cycles
WHERE status = 'running'
GROUP BY machine_id, cycle;
:::tip Common Session Patterns This pattern applies to many scenarios:
:::warning First Row Handling
The first row in each partition will have NULL for previous values. Always filter these out with WHERE prev_ts IS NOT NULL or similar conditions.
:::
:::info Related Documentation