Event analytics: choose the output row

• Sessions: one row per (user_id, session_index).
• Snapshots: one eligible event per device.
• Prerequisite: filters, grouping, timestamps, CTEs.

Predict the row count before choosing a window function.

Predict session boundaries

One user's times: 09:00, 09:30, 10:00, 10:31, 10:31.

Which events start a session? Does the first session exceed 30 minutes?

Trace the state

The rule limits adjacent gaps, not total session duration.

Keep one ordering throughout

1. LAG: partition by user, order by (event_ts, event_id).
2. Flag first rows and gaps strictly greater than 30 minutes.
3. Prefix-sum flags with the same order and a ROWS frame.
4. Group by user and session index.

Carry the tie breaker through each intermediate CTE.

A snapshot trap

At the midnight cutoff, a device has:

• 23:50: offline, ID 21
• 00:00: online, ID 22
• 00:00: error, ID 23
• 00:01: online, ID 24

Which row wins? What goes wrong if ranking happens before filtering?

Explain the answer

• Remove ID 24 before ranking.
• Order by timestamp descending, then event ID descending.
• ID 23 wins; all attributes come from that row.
• Ranking first would leave no eligible rank-1 row.

A final output sort cannot fix an ambiguous window order.

Practice and transfer

Build Event Sessions, then Latest Device State, then the checkpoint.

Before submitting, test: an exact 30-minute gap, tied times, another user, a cutoff-time event, and a device with only future events.