Running totals, moving averages, LAG/LEAD

Running totals and moving averages

A running total is SUM over a frame that grows from the partition’s start to the current row. From the frames lesson, always write ROWS for a sequential per-row total:

SELECT region, sale_date, amount,
       SUM(amount) OVER (
         PARTITION BY region ORDER BY sale_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales;

A moving average is the same idea with a bounded frame — average over the current row and the few before it:

-- 3-row trailing moving average of daily amount
SELECT sale_date, amount,
       AVG(amount) OVER (
         ORDER BY sale_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3
FROM sales;

The only change from a running total is the start bound: 2 PRECEDING instead of UNBOUNDED PRECEDING. Near the start of the partition the frame simply holds fewer rows (1, then 2, then the full 3) — correct edge behavior, not a bug.

LAG and LEAD: row-to-row deltas

LAG(col, n) reads a column from n rows before the current one in the window order; LEAD(col, n) reads n rows after. They are how you compute period-over-period change without a self-join:

-- month-over-month change in revenue
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS mom_pct
FROM monthly_revenue;

LAG(revenue) on the first row is NULL (there is no previous row) — give it a default with LAG(revenue, 1, 0) if you want 0 instead, and guard division with NULLIF so a zero previous value doesn’t divide-by-zero. LAG/LEAD ignore the frame entirely; they navigate by row offset within the ordered partition.

FIRST_VALUE, LAST_VALUE, and the trap

FIRST_VALUE(col) returns the column from the first row of the frame, LAST_VALUE(col) from the last. The trap: with an ORDER BY in the window, the default frame is RANGE UNBOUNDED PRECEDING AND CURRENT ROW, so the frame’s last row is the current row. LAST_VALUE therefore returns the current row’s value, not the partition’s final value — which almost no one wants.

-- WRONG: returns the current row's amount, because the frame ends at CURRENT ROW
LAST_VALUE(amount) OVER (PARTITION BY region ORDER BY sale_date)

-- RIGHT: extend the frame to the end of the partition
LAST_VALUE(amount) OVER (
  PARTITION BY region ORDER BY sale_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

FIRST_VALUE usually looks correct by accident: the frame starts at UNBOUNDED PRECEDING, which is genuinely the first row, so the default frame already covers it. It’s LAST_VALUE that bites — its target lives at the far end of the partition, beyond the default frame’s CURRENT ROW ceiling. Fix it by extending the frame to UNBOUNDED FOLLOWING, or sidestep it with MAX/MIN over the full partition (no ORDER BY, so the frame is the whole partition) when you want an extreme rather than the literal last-by-order row.