Lag, lead, and running totals

LAG looks at the previous row, LEAD looks at the next row. Combined with window frames, you can calculate month-over-month growth, running totals, and moving averages, which are the building blocks of time-series analysis.

LAG returns NULL for the first row by default. You can provide a default value as the third argument: LAG(revenue, 1, 0) returns 0 instead of NULL when there is no previous row.

lag_lead.sql
sql
-- Monthly revenue with LAG for month-over-month growth
WITH monthly_rev AS (
  SELECT
    DATE_TRUNC('month', o.order_purchase_timestamp) AS month,
    SUM(oi.price) AS revenue
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  GROUP BY DATE_TRUNC('month', o.order_purchase_timestamp)
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_rev,
  ROUND(
    ((revenue - LAG(revenue) OVER (ORDER BY month))
    / LAG(revenue) OVER (ORDER BY month) * 100)::NUMERIC, 1
  ) AS growth_pct
FROM monthly_rev
ORDER BY month;

LAG(col) OVER (ORDER BY ...) gets the value from the previous row. The first row returns NULL (no previous row). Perfect for growth calculations.

LEAD is the mirror image of LAG. Instead of looking backward, it looks at the next row. This is useful for calculating the gap between consecutive events.

lead_example.sql
sql
-- LEAD: days between consecutive orders per customer
WITH customer_orders AS (
  SELECT
    customer_id,
    order_purchase_timestamp,
    LEAD(order_purchase_timestamp) OVER (
      PARTITION BY customer_id
      ORDER BY order_purchase_timestamp
    ) AS next_order_date
  FROM orders
)
SELECT
  customer_id,
  order_purchase_timestamp,
  next_order_date,
  EXTRACT(DAY FROM (next_order_date - order_purchase_timestamp)) AS days_between
FROM customer_orders
WHERE next_order_date IS NOT NULL
LIMIT 20;

LEAD(col) looks at the NEXT row. PARTITION BY customer_id ensures we only compare orders from the same customer.

Beyond LAG and LEAD, window functions really shine with frame clauses. A running total uses SUM with a frame that stretches from the first row to the current row.

running_total.sql
sql
-- Running total: cumulative revenue over time
WITH monthly_rev AS (
  SELECT
    DATE_TRUNC('month', o.order_purchase_timestamp) AS month,
    SUM(oi.price) AS revenue
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  GROUP BY DATE_TRUNC('month', o.order_purchase_timestamp)
)
SELECT
  month,
  revenue,
  SUM(revenue) OVER (
    ORDER BY month
    ROWS UNBOUNDED PRECEDING
  ) AS cumulative_revenue
FROM monthly_rev
ORDER BY month;

ROWS UNBOUNDED PRECEDING means "from the first row to the current row." This creates a running total that grows with each row.

Quiz: Quiz

Loading practice…

Checkpoint: Window functions check

Loading practice…