SQL Foundations (Event Analytics)

Core workflow

• Define grain first
• Detect boundaries with windows
• Aggregate to final shape
• Order deterministically

Schema (Module)

Events:

• events(event_id, user_id, event_type, event_ts)

Devices:

• device_events(event_id, device_id, customer_id, status, recorded_at)

Sessionization recipe

• Partition by user_id
• Order by event_ts
• lag(event_ts) to compute previous timestamp
• is_new = 1 for first row or gap > 30 minutes
• session_index = sum(is_new) cumulative window
• Group by (user_id, session_index)

As-of latest-state recipe

• Filter rows to recorded_at <= cutoff
• row_number() per device_id by recorded_at desc
• Keep rn = 1
• Rename to last_status, last_seen_at

Common mistakes

• Using >= 30 minutes instead of > 30 minutes
• Ranking before cutoff filter
• Missing ORDER BY in output
• Missing PARTITION BY in windows