sess_events comes from a mobile SDK with at-least-once delivery: rows are stored in arrival order (ingested_ts), some events arrive late, and some are delivered twice (same event_id, different ingested_ts). Build sessions per user and report one row per session.
Rules, in order:
1. Dedupe — an event_id counts once. Different events can share a timestamp; they are not duplicates. 2. Event time — sessions follow event_ts, never arrival order. 3. Inactivity gap — an event more than 30 minutes after the user's previous event starts a new session (exactly 30 minutes continues it). 4. Length cap — a session may not span an hour. Split each gap-based session into consecutive 60-minute slices measured from its first event: slice 1 is [start, start + 60 min), slice 2 is [start + 60 min, start + 120 min), … Each non-empty slice is its own session.
Per session:
- •
user_id, session_no (1, 2, … per user in time order) - •
session_start, session_end — first and last event_ts - •
events — number of distinct events - •
duration_sec — seconds from start to end (integer) - •
has_purchase — boolean: any purchase event in the session
Sort by user_id, then session_no.