**1. Brief approach**Define sessions per user as contiguous events separated by less than a threshold (default 30 min), with rules to handle timezones, clock skew, intermittent connectivity, and bots. Compute canonical event timestamps in UTC, normalize device-local times, apply heuristics to merge out-of-order events, then window events into sessions using SQL (for batch) or Python (streaming).**2. Session boundary rules**- Default inactivity threshold: 30 minutes (configurable).- Hard max session length: 8 hours (force split).- If events arrive out-of-order within a small slack (e.g., 5 min), reorder by corrected timestamp before windowing.- For devices with long offline gaps but same intent (mobile reconnect within 2 min of last activity on different network), treat as same session if event_types indicate continuation (e.g., page_view → form_submit).**3. Bot detection heuristics**- High-frequency events: > 100 events/min => bot.- Uniform inter-event intervals (low variance) => bot.- Unusual user-agent patterns or missing UA + repeated API-only event_types.- IP churn: many user_ids from same IP in short time.- Very short sessions (<2s) with many identical events.Label bots early, exclude from research sessions, but keep raw for audit.**4. Scalable implementation (SQL batch)**- Normalize times to UTC using user_tz when available, else infer from IP first event.- Use window functions to compute gap:sql
WITH normalized AS (
SELECT user_id, event_time_utc, event_type,
LAG(event_time_utc) OVER (PARTITION BY user_id ORDER BY event_time_utc) AS prev_ts
FROM events
),
gaps AS (
SELECT *,
EXTRACT(EPOCH FROM (event_time_utc - prev_ts))/60 AS gap_minutes,
SUM(CASE WHEN EXTRACT(EPOCH FROM (event_time_utc - prev_ts))/60 > 30 THEN 1 ELSE 0 END)
OVER (PARTITION BY user_id ORDER BY event_time_utc) AS session_group
FROM normalized
)
SELECT user_id, session_group,
MIN(event_time_utc) AS session_start, MAX(event_time_utc) AS session_end,
COUNT(*) AS events
FROM gaps
GROUP BY user_id, session_group;
python
# Python pseudocode
def process_event(state, event):
ts = to_utc(event)
if ts < state.last_ts - skew_tol: handle_late(event)
gap = ts - state.last_ts
if gap <= timedelta(minutes=30) and state.duration < max_session:
state.extend(event)
else:
emit_session(state); state.start_new(event)
