Sessionize retries and exact boundaries

Assign each canonical website event a session_no for its shopper. Return shopper_id, event_id and session_no, in shopper and event order.

what to return

Columns: shopper_idevent_idsession_no in this order
Rows: 36 rows · in the order asked
Watch for: Deduplicate globally by event_key, keeping the smallest id; a retry may have a conflicting payload.
: Use (occurred_at, id) as the total event order; equal timestamps follow id order. All times are UTC.
: A shopper starts a session at the first canonical event or after a gap strictly greater than 30 minutes from their previous canonical event. Exactly 30 minutes stays in the same session; midnight alone does not split it.
: Session numbers start at 1 per shopper and include every event type, including heartbeat and purchase-only sessions.
: Order by shopper_id, occurred_at, id; occurred_at is only a sort key, not an output column.
Skill: Build stable session identifiers from canonical events and inactivity gaps. Refresher: Sessionize an event stream →

expected result

Your query should return exactly these columns and values. Run it and compare.

expected result36 rows · in this order

first 3 of 36 rows

data you can query

Sample rows from each table. Each column shows its type underneath; → marks a link to another table.

market_web_events7 columns · 38 rows

idinteger · key	event_keytext	shopper_idinteger → shoppers	occurred_attext	event_typetext	channeltext	cart_idtext
1	a1	1	2025-05-01 09:00:00	view	email	A
2	a2	1	2025-05-01 09:05:00	checkout	direct	A
3	a3	1	2025-05-01 09:10:00	purchase	direct	A
4	a3	1	2025-05-01 09:10:00	purchase	direct	A
5	b1	1	2025-05-01 10:00:00	view	search	B

showing 5 of 38 rows

solution.sql

Hints and the answer are always available. Using them counts the question as solved with help.