LAG function for previous row access in SQL - Step-by-Step Execution

Concept Flow - LAG function for previous row access

Start with ordered rows

↓

For each row

↓

Look at previous row in order

↓

Return previous row's value

↓

If no previous row, return NULL or default

↓

Output current row with previous value

↓

Repeat for all rows

↓

End

The LAG function looks at the previous row's value in a defined order and returns it for the current row, or NULL if there is no previous row.

Execution Sample

SQL

SELECT id, sales, LAG(sales) OVER (ORDER BY id) AS prev_sales
FROM sales_data;

This query returns each row's sales and the sales value from the previous row ordered by id.

Execution Table

Step	Current Row (id)	Current sales	Previous Row sales (LAG)	Output Row (id, sales, prev_sales)
1	1	100	NULL	(1, 100, NULL)
2	2	150	100	(2, 150, 100)
3	3	200	150	(3, 200, 150)
4	4	130	200	(4, 130, 200)
5	5	170	130	(5, 170, 130)
6	End	-	-	No more rows, stop

💡 Reached end of rows; no more previous rows to access.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	Final
Current Row id	N/A	1	2	3	4	5	End
Current sales	N/A	100	150	200	130	170	N/A
LAG(sales)	N/A	NULL	100	150	200	130	N/A

Key Moments - 3 Insights

Why does the first row's LAG value return NULL?

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

How does the LAG function know which row is previous?

It uses the ORDER BY clause inside the OVER() clause to define the order, so the previous row is the one with the immediately smaller id, as seen in the execution_table.

What happens if the order is changed?

The previous row changes according to the new order, so the LAG values will reflect the previous row in that new order, changing the output rows accordingly.

Visual Quiz - 3 Questions

Look at the execution_table, what is the LAG(sales) value at step 3?

A 200

B 150

C 100

D NULL

💡 Hint

Check the 'Previous Row sales (LAG)' column at step 3 in the execution_table.

At which step does the LAG function first return a non-NULL value?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look at the 'Previous Row sales (LAG)' column in execution_table for the first non-NULL value.

If the ORDER BY was changed to ORDER BY sales DESC, how would the LAG value for id=2 change?

A It would be 170

B It would be NULL

C It would be 150

D It would be 100

💡 Hint

Think about the new order by sales descending and which row comes before id=2 in that order.

Concept Snapshot

LAG(value) OVER (ORDER BY column) returns the previous row's value in the specified order.
If no previous row exists, it returns NULL by default.
Useful for comparing current row with previous row in ordered data.
ORDER BY inside OVER() defines the row sequence.
Can specify default value as third argument to LAG.
Helps analyze trends or differences between rows.

Full Transcript

The LAG function in SQL lets you access the value from the previous row in a result set ordered by a column. It works by looking at each row in order and returning the value from the row before it. If there is no previous row, it returns NULL. For example, if you have sales data ordered by id, LAG(sales) will give you the sales from the previous id. This is useful to compare current and previous values easily. The order is important because it defines which row is considered previous. Changing the order changes the LAG results. Beginners often wonder why the first row returns NULL and how the previous row is determined. The execution table shows step by step how each row's previous value is found. This helps understand the flow and output clearly.

Practice

1. What does the SQL LAG function do?

easy

A. Returns the value from the next row in the result set

B. Calculates the sum of all previous rows

C. Returns the value from the previous row in the result set

D. Filters rows based on a condition

Solution

1. Step 1: Understand the purpose of LAG

   The LAG function is used to access data from a previous row relative to the current row in a query result.

2. Step 2: Compare options with definition

   Returns the value from the previous row in the result set correctly states that LAG returns the previous row's value. Other options describe different functions or behaviors.

3. Final Answer:

   Returns the value from the previous row in the result set -> Option C

4. Quick Check:

   LAG = previous row value [OK]

Hint: LAG looks backward one row by default [OK]

Common Mistakes:

• Confusing LAG with LEAD (next row)
• Thinking LAG sums values
• Assuming LAG filters rows

2. Which of the following is the correct syntax to use LAG to get the previous salary ordered by employee ID?

easy

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

B. LAG(salary) BY employee_id

C. LAG(salary) ORDER employee_id

D. LAG(salary) PARTITION BY employee_id

Solution

1. Step 1: Recall LAG syntax

   LAG requires an OVER clause with ORDER BY to define the row order for previous row access.

2. Step 2: Match syntax with options

   LAG(salary) OVER (ORDER BY employee_id) correctly uses OVER (ORDER BY employee_id). The other options misuse syntax or keywords.

3. Final Answer:

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

4. Quick Check:

   LAG syntax = LAG() OVER (ORDER BY ...) [OK]

Hint: Always use OVER with ORDER BY for LAG [OK]

Common Mistakes:

• Omitting OVER clause
• Using PARTITION BY without ORDER BY
• Incorrect keyword order

3. Given the table Employees with columns id and salary, what will this query return?

SELECT id, salary, LAG(salary) OVER (ORDER BY id) AS prev_salary FROM Employees;

Assuming data:
id | salary
1 | 5000
2 | 6000
3 | 5500

medium

A. [{id:1, salary:5000, prev_salary:NULL}, {id:2, salary:6000, prev_salary:5000}, {id:3, salary:5500, prev_salary:6000}]

B. [{id:1, salary:5000, prev_salary:5000}, {id:2, salary:6000, prev_salary:6000}, {id:3, salary:5500, prev_salary:5500}]

C. [{id:1, salary:5000, prev_salary:6000}, {id:2, salary:6000, prev_salary:5500}, {id:3, salary:5500, prev_salary:NULL}]

D. [{id:1, salary:5000, prev_salary:NULL}, {id:2, salary:6000, prev_salary:NULL}, {id:3, salary:5500, prev_salary:NULL}]

Solution

1. Step 1: Understand LAG with ORDER BY id

   LAG(salary) returns the salary from the previous row ordered by id. For id=1, no previous row exists, so NULL is returned.

2. Step 2: Apply LAG to each row

   Row 1: prev_salary = NULL
   Row 2: prev_salary = salary of id=1 = 5000
   Row 3: prev_salary = salary of id=2 = 6000

3. Final Answer:

   [{id:1, salary:5000, prev_salary:NULL}, {id:2, salary:6000, prev_salary:5000}, {id:3, salary:5500, prev_salary:6000}] -> Option A

4. Quick Check:

   LAG returns previous row's value or NULL if none [OK]

Hint: First row prev value is always NULL [OK]

Common Mistakes:

• Assuming first row has previous value
• Mixing up current and previous rows
• Ignoring ORDER BY effect

4. Identify the error in this query:

SELECT id, salary, LAG(salary) ORDER BY id AS prev_salary FROM Employees;

medium

A. AS alias must come before LAG

B. ORDER BY cannot be used inside LAG

C. LAG cannot be used with salary column

D. Missing OVER() clause for LAG function

Solution

1. Step 1: Check LAG function syntax

   LAG requires an OVER() clause to specify ordering. The query uses ORDER BY without OVER(), which is invalid.

2. Step 2: Confirm correct syntax

   The correct syntax is LAG(salary) OVER (ORDER BY id). The query misses OVER(), causing a syntax error.

3. Final Answer:

   Missing OVER() clause for LAG function -> Option D

4. Quick Check:

   LAG needs OVER() with ORDER BY [OK]

Hint: LAG always needs OVER() clause [OK]

Common Mistakes:

• Using ORDER BY without OVER()
• Placing alias before function
• Misunderstanding LAG usage

5. You want to find the difference between each employee's salary and the previous employee's salary ordered by id. Which query correctly calculates this difference?

hard

A. SELECT id, salary, LAG(salary) - salary OVER (ORDER BY id) AS diff FROM Employees;

B. SELECT id, salary, salary - LAG(salary) OVER (ORDER BY id) AS diff FROM Employees;

C. SELECT id, salary, salary - LAG(salary) FROM Employees ORDER BY id;

D. SELECT id, salary, salary - LAG(salary) PARTITION BY id AS diff FROM Employees;

Solution

1. Step 1: Understand the goal

   We want to subtract the previous salary from the current salary, ordered by id.

2. Step 2: Check each option's correctness

   SELECT id, salary, salary - LAG(salary) OVER (ORDER BY id) AS diff FROM Employees; correctly uses LAG(salary) OVER (ORDER BY id) and subtracts it from salary.
   SELECT id, salary, LAG(salary) - salary OVER (ORDER BY id) AS diff FROM Employees; reverses subtraction order.
   SELECT id, salary, salary - LAG(salary) FROM Employees ORDER BY id; misses OVER clause.
   SELECT id, salary, salary - LAG(salary) PARTITION BY id AS diff FROM Employees; misuses PARTITION BY without OVER.

3. Final Answer:

   SELECT id, salary, salary - LAG(salary) OVER (ORDER BY id) AS diff FROM Employees; -> Option B

4. Quick Check:

   Difference = current - previous salary using LAG [OK]

Hint: Use LAG with OVER and subtract current minus previous [OK]

Common Mistakes:

• Forgetting OVER() clause
• Subtracting in wrong order
• Misusing PARTITION BY without OVER