SQL Window Functions for Event Analysis

Window functions calculate across related rows while preserving each row in the result. A regular GROUP BY reduces many events to one row per group; a window can keep each day, request, or account while adding a previous value, rank, or rolling baseline.

Compare adjacent buckets

WITH daily AS (
  SELECT
    date_trunc('day', timestamp_utc) AS day,
    COUNT(*) AS completed_jobs
  FROM job_events
  WHERE status = 'completed'
    AND timestamp_utc >= now() - INTERVAL '30 days'
  GROUP BY date_trunc('day', timestamp_utc)
)
SELECT
  day,
  completed_jobs,
  LAG(completed_jobs) OVER (ORDER BY day) AS previous_day_jobs,
  AVG(completed_jobs) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7_bucket_average
FROM daily
ORDER BY day;

LAG reads a prior row according to the window order. A framed AVG calculates a rolling seven-bucket baseline. The first rows have fewer than seven contributing buckets, so label or suppress them when a full window is required.

Partition by the comparison unit

Add PARTITION BY service when each service needs an independent sequence. Without it, the previous row may belong to another service. The ordering columns should also be deterministic; if two events share a timestamp, add a stable identifier as a tie-breaker.

ROW_NUMBER is useful for choosing one event per logical key:

ROW_NUMBER() OVER (
  PARTITION BY delivery_id
  ORDER BY timestamp_utc DESC, event_id DESC
) AS newest_rank

Filter to newest_rank = 1 in an outer query. Decide whether “newest” or “first accepted” is the correct business rule before using the pattern.

Windows do not repair missing time buckets, duplicated source events, or an ambiguous row grain. Validate those conditions before interpreting a period-over-period change. Read SQL Deduplication for identity rules and the DataFusion SQL reference for the window syntax tested by Telemetry.