Skip to Content

Time-Series Features: LAG / LEAD / INTERVAL

When to use

Build ‘time since last event’, ‘previous status’, or ‘next-hour forecast’ features for sequence modelling, fraud detection, or forecasting.

Analogy

LAG looks back at the previous item on the conveyor belt; LEAD peeks at the next one.

Data-flow diagram

  ts          amount   LAG-amount    LEAD-amount
  --------    -------   -----------   ------------
  09:00:00    100      NULL          200
  10:00:00    200      100           50
  11:00:00     50      200           500
  12:00:00    500       50           NULL  -- last row

  LAG(col, n) OVER (PARTITION BY ... ORDER BY ts)

Deep explanation

LAG and LEAD are the primitive operations for time-derived features: previous-row queries in O(1) per row. LAG(col, n) returns the value n rows back; LAG(col, n, default) returns a default instead of NULL. Combine with INTERVAL, NOW(), DATE_TRUNC, and EXTRACT(epoch FROM ...) to turn raw event streams into modelling-ready features. Time-bucket with DATE_TRUNC for cumulative features; partition by entity so each user/IoT device gets its own sequence.

Examples

Example 1

-- 12a: time since previous event per user
SELECT user_id, ts, kind,
       ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) AS gap
  FROM events;

The first-row ‘gap’ is NULL; coalesce in downstream code or use LAG(ts, 1, ts) for a self-referencing default.

Example 2

-- 12b: previous amount (with default) and rolling delta
SELECT user_id, ts, amount,
       LAG(amount, 1, 0) OVER (PARTITION BY user_id ORDER BY ts) AS prev_amt,
       amount - LAG(amount, 1, 0) OVER (PARTITION BY user_id ORDER BY ts) AS delta
  FROM payments;

Default 0 in LAG(amount, 1, 0) is sensible for monetary deltas where ‘no previous’ can be treated as zero.

Example 3

-- 12c: hour-of-day activity histogram per user
SELECT user_id,
       EXTRACT(HOUR FROM ts)        AS hr,
       COUNT(*)                      AS events,
       SUM(amount) FILTER (WHERE kind='purchase') AS spend
  FROM events
 WHERE ts >= NOW() - INTERVAL '30 days'
 GROUP BY user_id, EXTRACT(HOUR FROM ts)
 ORDER BY user_id, hr;

Hour-of-day histogram converts sparse events into 24 dense features per user — a feed to tree-based models.

Common mistake

Forgetting PARTITION BY on the window — LAG returns the previous row in the entire table, not per user. Another trap: missing ORDER BY — Postgres still runs LAG but the ‘previous row’ is undefined and changes between runs. Watch NULL semantics: LAG returns NULL for the first row; don’t silently treat it as 0 if that biases the model.

Key takeaway

LAG for ‘previous’, LEAD for ‘next’; always PARTITION BY entity + ORDER BY ts in time-series; use the 3-argument LAG(col, n, default) to avoid NULL surprises; combine with EXTRACT/DATE_TRUNC to derive calendar features.

Production Failure Playbook

Failure scenario 1: lag-without-partition

Failure scenario 2: first-row-null-bias