LAG, LEAD, and running totals

These window functions look at neighboring rows or accumulate values over an ordered sequence.

LAG() — look at the previous row

LAG(col, n, default) returns the value of col from n rows before the current row:

SELECT
  first_name,
  hire_date,
  salary,
  LAG(salary) OVER (ORDER BY hire_date) AS prev_hire_salary,
  salary - LAG(salary) OVER (ORDER BY hire_date) AS salary_diff
FROM employees
ORDER BY hire_date;

LEAD() — look at the next row

LEAD(col, n, default) returns the value from n rows ahead:

SELECT
  order_date,
  id AS order_id,
  LEAD(order_date) OVER (ORDER BY order_date, id) AS next_order_date
FROM orders;

Default when there is no neighbor

The third argument sets the value when no prior/next row exists (default is NULL):

LAG(salary, 1, 0) OVER (ORDER BY hire_date)  -- returns 0 for the first row

Running total with SUM() OVER

SELECT
  order_date,
  id,
  COUNT(*) OVER (ORDER BY order_date, id) AS running_count
FROM orders
ORDER BY order_date, id;

Running total with actual amounts

SELECT
  hire_date,
  salary,
  SUM(salary) OVER (ORDER BY hire_date, id) AS cumulative_payroll
FROM employees
ORDER BY hire_date;

Tips

• PARTITION BY resets the running total at the start of each group.
• LAG/LEAD with PARTITION BY looks at neighbors within the partition only.
• For period-over-period analysis: compare current row value to LAG(value, 12) for monthly year-over-year.