Concept · SQL & Querying

LAG/LEAD default to NULL at the partition edge; offset >1 and IGNORE NULLS are the controls (dialect note).

The edge NULL is LAG saying 'no predecessor' — sometimes exactly right (a delta against nothing is unknown), sometimes wrong for the arithmetic downstream (a consumption calc where NULL poisons the first day). The third argument states the policy at the source: LAG(x, 1, 0) reads 'and before the beginning, zero'. Two adjacent facts: the second argument is the offset (n rows back), and the default fires only where the row is missing, not where its value is NULL.

See it break

oil

night	litres
1	100
2	90
3	75

The trap

SELECT
  night,
  litres - LAG (litres) OVER (
    ORDER BY
      night
  ) AS used
FROM
  oil
ORDER BY
  night;

The fix

SELECT
  night,
  litres - LAG (litres, 1, litres) OVER (
    ORDER BY
      night
  ) AS used
FROM
  oil
ORDER BY
  night;

INPUT	Your query	The fix
1	NULL	0
2	-10	-10
3	-15	-15

Night one's consumption came out NULL — LAG defaults to NULL at the partition edge (no predecessor), and here that NULL then poisons the arithmetic; the third argument LAG(litres, 1, litres) states the baseline instead.

The rule

The 3-arg form with a meant default; COALESCE for value-NULLs; offset for n-back comparisons.

Shows up elsewhere

LAG detects the gap NULL propagates in expressions

Try one →Prove it in practice →