What are LAG, LEAD and running totals?

Definition:

LAG and LEAD read values from earlier or later rows within an ordered window. A running total uses an aggregate window that expands through the current row. These calculations support trends, comparisons and cumulative reports while preserving detail records.

Key points:

• LAG reads a previous row's value; LEAD reads a later row's value.

• A running total adds values from the start through the current row.

• Use a stable order and an explicit window frame when row-by-row totals matter.

• An explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame gives a row-by-row running total; stable ordering resolves ties.

Example:

SELECT id, customer_id, amount,
       SUM(amount) OVER (
         PARTITION BY customer_id
         ORDER BY created_at, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
ORDER BY customer_id, created_at, id;

• Each customer has a separate running total; id breaks timestamp ties.

Interview point:

• Explain both the partition boundary and ordering, because “previous” has no meaning without them.