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.

Practice

1. What is the main purpose of the LAG function in PostgreSQL?

easy

A. To sort rows in ascending order

B. To access data from the next row in the same result set

C. To delete duplicate rows

D. To access data from a previous row in the same result set

Solution

1. Step 1: Understand LAG function behavior

   The LAG function returns the value from a previous row based on the current row's position.

2. Step 2: Compare with other options

   LEAD accesses the next row, sorting and deleting duplicates are unrelated to LAG.

3. Final Answer:

   To access data from a previous row in the same result set -> Option D

4. Quick Check:

   LAG = previous row data access [OK]

Hint: LAG looks backward, LEAD looks forward in rows [OK]

Common Mistakes:

• Confusing LAG with LEAD
• Thinking LAG sorts rows
• Assuming LAG deletes duplicates

2. Which of the following is the correct syntax to use LEAD to get the next row's salary ordered by employee_id?

easy

A. LEAD(salary) OVER (ORDER BY employee_id)

B. LEAD(salary ORDER BY employee_id)

C. LEAD(salary) ORDER BY employee_id

D. LEAD(ORDER BY salary) OVER (employee_id)

Solution

1. Step 1: Recall correct LEAD syntax

   The correct syntax uses LEAD(column) OVER (ORDER BY column) to specify the column and ordering.

2. Step 2: Check each option

   LEAD(salary) OVER (ORDER BY employee_id) matches the correct syntax; others misuse parentheses or clause order.

3. Final Answer:

   LEAD(salary) OVER (ORDER BY employee_id) -> Option A

4. Quick Check:

   LEAD(column) OVER (ORDER BY column) [OK]

Hint: LEAD/LAG need OVER with ORDER BY inside parentheses [OK]

Common Mistakes:

• Placing ORDER BY outside OVER clause
• Missing parentheses around ORDER BY
• Putting ORDER BY inside function parameters

3. Given the table sales with columns month and revenue, what will this query return?

SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue FROM sales;

medium

A. Each row shows only current month revenue

B. Each row shows current month revenue and next month's revenue

C. Each row shows current month revenue and previous month's revenue

D. Query will cause a syntax error

Solution

1. Step 1: Understand LAG with ORDER BY month

   The LAG(revenue) returns the revenue from the previous month ordered by month.

2. Step 2: Interpret query output

   Each row shows current month revenue and previous month's revenue in prev_revenue. The first row's prev_revenue will be NULL.

3. Final Answer:

   Each row shows current month revenue and previous month's revenue -> Option C

4. Quick Check:

   LAG = previous row value [OK]

Hint: LAG returns previous row's value based on ORDER BY [OK]

Common Mistakes:

• Confusing LAG with LEAD
• Expecting next month's revenue instead
• Thinking query causes error

4. Identify the error in this query:

SELECT employee_id, salary, LEAD(salary) ORDER BY employee_id FROM employees;

medium

A. ORDER BY should be inside LEAD() function

B. Missing OVER() clause for LEAD function

C. LEAD cannot be used with salary column

D. employee_id must be in GROUP BY clause

Solution

1. Step 1: Check LEAD function syntax

   LEAD requires an OVER() clause to specify ordering and partitioning.

2. Step 2: Analyze query structure

   The query uses LEAD(salary) without OVER(), causing a syntax error.

3. Final Answer:

   Missing OVER() clause for LEAD function -> Option B

4. Quick Check:

   Window functions need OVER() [OK]

Hint: Always use OVER() with LEAD/LAG [OK]

Common Mistakes:

• Omitting OVER() clause
• Placing ORDER BY outside OVER()
• Confusing window functions with aggregates

5. You want to find all employees whose salary increased compared to the previous employee ordered by hire_date. Which query correctly uses LAG to achieve this?

hard

A. SELECT employee_id, salary FROM (SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees) t WHERE salary > prev_salary;

B. SELECT employee_id, salary FROM employees WHERE salary > (SELECT LAG(salary) FROM employees ORDER BY hire_date);

C. SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees WHERE salary > prev_salary;

D. SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees HAVING salary > prev_salary;

Solution

1. Step 1: Understand LAG usage for filtering

   Window functions like LAG cannot be used directly in WHERE clause; use subquery or CTE to compute then filter.

2. Step 2: Analyze options

   SELECT employee_id, salary FROM (SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees) t WHERE salary > prev_salary; correctly uses subquery. The second option's subquery LAG lacks OVER clause (syntax error). The third uses alias in WHERE (invalid). The fourth uses HAVING without GROUP BY (invalid).

3. Final Answer:

   SELECT employee_id, salary FROM (SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees) t WHERE salary > prev_salary; -> Option A

4. Quick Check:

   Subquery to filter on window functions [OK]

Hint: Use subquery to filter on window function results [OK]

Common Mistakes:

• Using alias in WHERE clause
• Misusing HAVING without GROUP BY
• Incorrect subquery for LAG