DocsWindow FunctionsLAG & LEAD

LAG & LEAD

Window Functions

Concept

LAG accesses a value from a previous row in the partition, and LEAD accesses a value from a subsequent row, both relative to the current row according to the ORDER BY in the OVER clause. They take three arguments: the column to read, the offset (defaulting to 1), and an optional default value when there is no previous/next row.

These functions are invaluable for time-series analysis and change detection. With LAG you can calculate day-over-day changes, month-over-month growth rates, and gaps between events. With LEAD you can look ahead to compute time-to-next-event or identify upcoming changes.

LAG and LEAD are evaluated based on the window ordering, not the final query ORDER BY. Make sure the OVER clause's ORDER BY matches the chronological or logical sequence you intend. PARTITION BY is optional: use it when you need to compare within groups (e.g., per customer, per product) rather than globally.

Syntax

-- LAG: previous row's value
SELECT
  order_date,
  total_amount,
  LAG(total_amount) OVER (ORDER BY order_date) AS prev_amount
FROM orders;

-- LEAD: next row's value with default
SELECT
  order_date,
  total_amount,
  LEAD(total_amount, 1, 0) OVER (ORDER BY order_date) AS next_amount
FROM orders;

-- With partition
SELECT
  customer_id,
  order_date,
  total_amount,
  LAG(order_date) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS prev_order_date
FROM orders;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Month-over-month revenue change
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    / LAG(revenue) OVER (ORDER BY month) * 100, 1
  ) AS growth_pct
FROM (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
) monthly;

-- Days between consecutive orders per customer
SELECT
  customer_id,
  order_date,
  order_date - LAG(order_date) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS days_since_last_order
FROM orders
ORDER BY customer_id, order_date;

Common Pitfalls & Tips

1The first row in a partition has no LAG value (returns NULL). The last row has no LEAD value. Provide a default third argument or use COALESCE to handle these edge cases.
2LAG/LEAD offsets must be non-negative. Use LAG for lookback and LEAD for lookahead — do not try negative offsets.
3Dividing by LAG() for growth rates can cause division-by-zero if the previous value is 0. Guard with NULLIF or CASE.

Practice LAG & LEAD queries