SQL Foundations for Event Analytics

Event analytics SQL is mostly about three things:

1. Pick the right grain.
2. Use windows to create sequence-aware features.
3. Make results deterministic and auditable.

This module is intentionally practical and maps directly to the first two problems (event-sessions, device-last-state).

Learning goals

• Define output grain before writing any SQL.
• Segment clickstream events into sessions with lag + cumulative sum.
• Build as-of snapshots with cutoff filtering + row_number.
• Avoid common mistakes around ordering, cutoff boundaries, and grouping.
• Produce stable outputs with explicit ordering.

Habit 1: define grain first

Write this sentence before coding:

One output row represents ...

Examples in this module:

• Sessionization problem:

One row = one (user_id, session_index) session

• Device snapshot problem:

One row = one device_id as-of a cutoff time

If you skip grain definition, you usually group by the wrong keys and get duplicate or missing rows.

Habit 2: separate "detect" and "aggregate"

For event streams, use two phases:

1. Detect row-level boundaries with window functions.
2. Aggregate rows into business entities.

For sessions:

• Detect boundary: compare current event_ts with previous event timestamp per user using lag.
• Convert boundary to integer marker is_new (0/1).
• Create session_index via cumulative sum(is_new) over time.
• Aggregate by (user_id, session_index).

Sessionization blueprint (near-complete)

WITH ordered AS (
  SELECT
    user_id,
    event_ts,
    CASE
      WHEN lag(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) IS NULL THEN 1
      WHEN event_ts - lag(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) > INTERVAL '30 minutes' THEN 1
      ELSE 0
    END AS is_new
  FROM events
),
numbered AS (
  SELECT
    user_id,
    event_ts,
    SUM(is_new) OVER (
      PARTITION BY user_id
      ORDER BY event_ts
      ROWS UNBOUNDED PRECEDING
    ) AS session_index
  FROM ordered
)
SELECT
  user_id,
  session_index,
  MIN(event_ts) AS session_start,
  MAX(event_ts) AS session_end,
  COUNT(*) AS event_count
FROM numbered
GROUP BY user_id, session_index
ORDER BY user_id, session_index;

Important boundary rule: session breaks on gap > 30 minutes (not >=).

Habit 3: as-of logic must filter early

As-of questions are easiest when you:

1. Filter to rows at or before cutoff.
2. Rank rows per entity by recency.
3. Keep the top row.

As-of latest-state blueprint (near-complete)

WITH ranked AS (
  SELECT
    device_id,
    customer_id,
    status,
    recorded_at,
    ROW_NUMBER() OVER (
      PARTITION BY device_id
      ORDER BY recorded_at DESC
    ) AS rn
  FROM device_events
  WHERE recorded_at <= TIMESTAMPTZ '2025-06-01 00:00:00+00'
)
SELECT
  device_id,
  customer_id,
  status AS last_status,
  recorded_at AS last_seen_at
FROM ranked
WHERE rn = 1
ORDER BY device_id;

Filtering after ranking is a common bug because future rows can incorrectly win.

Determinism checklist

Before finalizing a query, verify:

• every window function has explicit PARTITION BY and ORDER BY
• every final result has explicit ORDER BY
• all tie behavior is intentional
• timestamp boundaries (<=, >) match statement exactly

Failure modes to watch

• Grouping too early (lose row-level sequence information).
• Using >= 30 minutes for session split when requirement is > 30 minutes.
• Forgetting partition in windows and mixing users/devices together.
• Applying cutoff in outer query after ranking.
• Returning non-deterministic row order.

What success looks like

You should be able to:

• read a stream table and state exact output grain in one sentence
• derive boundary flags from lag
• turn flags into stable indexes with cumulative windows
• answer "latest state as-of time T" with ranking logic