LAG and LEAD for row comparison in PostgreSQL - Step-by-Step Execution

Concept Flow - LAG and LEAD for row comparison

Start with ordered rows

↓

Apply LAG function

→Get previous row value

↓

Apply LEAD function

→Get next row value

↓

Compare current row with previous and next

↓

Output rows with comparison columns

The flow shows how LAG and LEAD fetch previous and next row values in an ordered set, enabling row-to-row comparisons.

Execution Sample

PostgreSQL

SELECT id, value,
       LAG(value) OVER (ORDER BY id) AS prev_value,
       LEAD(value) OVER (ORDER BY id) AS next_value
FROM sample_table
ORDER BY id;

This query fetches each row's value along with the previous and next row's value based on id order.

Execution Table

Step	Row id	Current value	LAG(value)	LEAD(value)	Explanation
1	1	10	NULL	20	First row has no previous, so LAG is NULL; next value is 20
2	2	20	10	30	Previous value is 10; next value is 30
3	3	30	20	40	Previous value is 20; next value is 40
4	4	40	30	NULL	Last row has no next, so LEAD is NULL; previous value is 30
5	END				No more rows to process

💡 Reached end of rows; no further previous or next values available

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	Final
id	N/A	1	2	3	4	4
value	N/A	10	20	30	40	40
LAG(value)	N/A	NULL	10	20	30	30
LEAD(value)	N/A	20	30	40	NULL	NULL

Key Moments - 3 Insights

Why is LAG(value) NULL for the first row?

Because there is no previous row before the first row, LAG returns NULL as shown in execution_table row 1.

Why is LEAD(value) NULL for the last row?

Because there is no next row after the last row, LEAD returns NULL as shown in execution_table row 4.

How does the ORDER BY clause affect LAG and LEAD?

ORDER BY defines the row sequence for LAG and LEAD to find previous and next rows, as seen in the consistent id ordering in execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the LEAD(value) for row with id=2?

A 10

B 30

C 20

D 40

💡 Hint

Check the row where Step=2 in the execution_table under LEAD(value) column.

At which row does LAG(value) first become non-NULL?

A Row with id=2

B Row with id=1

C Row with id=3

D Row with id=4

💡 Hint

Look at the LAG(value) column in execution_table rows 1 and 2.

If the ORDER BY was changed to DESC, what would happen to the LAG and LEAD values?

A LAG and LEAD would both return NULL

B They would remain the same

C LAG would get next row values, LEAD would get previous row values

D Only LEAD values would change

💡 Hint

Consider how ORDER BY affects row sequence and thus which rows are previous or next.

Concept Snapshot

LAG and LEAD functions let you access previous or next row values in a result set.
Use OVER(ORDER BY column) to define row order.
LAG returns NULL if no previous row; LEAD returns NULL if no next row.
Useful for comparing current row with neighbors in ordered data.

Full Transcript

This visual execution shows how the PostgreSQL functions LAG and LEAD work to compare rows. Starting with rows ordered by id, LAG fetches the previous row's value, and LEAD fetches the next row's value. For the first row, LAG returns NULL because there is no previous row. For the last row, LEAD returns NULL because there is no next row. The ORDER BY clause inside the OVER() defines the sequence for these functions. The execution table traces each row's current value, previous value via LAG, and next value via LEAD. Variable tracking shows how these values change step-by-step. Key moments clarify common confusions about NULL results and ordering. The quiz tests understanding of these concepts by referencing the execution table and variable tracker.