LAG function for previous row access in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of LAG function with partition

Consider a sales table with columns region, month, and sales. What is the output of the following query?

SELECT region, month, sales, LAG(sales) OVER (PARTITION BY region ORDER BY month) AS prev_sales FROM sales ORDER BY region, month;

SQL

CREATE TABLE sales (region VARCHAR(10), month INT, sales INT);
INSERT INTO sales VALUES
('East', 1, 100),
('East', 2, 150),
('West', 1, 200),
('West', 2, 180);

SELECT region, month, sales, LAG(sales) OVER (PARTITION BY region ORDER BY month) AS prev_sales FROM sales ORDER BY region, month;

A [{"region": "East", "month": 1, "sales": 100, "prev_sales": 150}, {"region": "East", "month": 2, "sales": 150, "prev_sales": 100}, {"region": "West", "month": 1, "sales": 200, "prev_sales": 180}, {"region": "West", "month": 2, "sales": 180, "prev_sales": 200}]

B [{"region": "East", "month": 1, "sales": 100, "prev_sales": null}, {"region": "East", "month": 2, "sales": 150, "prev_sales": 100}, {"region": "West", "month": 1, "sales": 200, "prev_sales": null}, {"region": "West", "month": 2, "sales": 180, "prev_sales": 200}]

C [{"region": "East", "month": 1, "sales": 100, "prev_sales": null}, {"region": "East", "month": 2, "sales": 150, "prev_sales": null}, {"region": "West", "month": 1, "sales": 200, "prev_sales": null}, {"region": "West", "month": 2, "sales": 180, "prev_sales": null}]

D [{"region": "East", "month": 1, "sales": 100, "prev_sales": 100}, {"region": "East", "month": 2, "sales": 150, "prev_sales": 150}, {"region": "West", "month": 1, "sales": 200, "prev_sales": 200}, {"region": "West", "month": 2, "sales": 180, "prev_sales": 180}]

💡 Hint

Remember that LAG returns the value from the previous row within the same partition ordered by the specified column.

The LAG function returns the previous row's sales value within each region partition ordered by month. For the first month in each region, there is no previous row, so it returns null.

🧠 Conceptual

intermediate

Understanding default value in LAG function

What will be the output of the prev_sales column in the following query if the default value is set to 0?

SELECT month, sales, LAG(sales, 1, 0) OVER (ORDER BY month) AS prev_sales FROM sales ORDER BY month;

SQL

CREATE TABLE sales (month INT, sales INT);
INSERT INTO sales VALUES
(1, 100),
(2, 150),
(3, 200);

SELECT month, sales, LAG(sales, 1, 0) OVER (ORDER BY month) AS prev_sales FROM sales ORDER BY month;

A [{"month": 1, "sales": 100, "prev_sales": 0}, {"month": 2, "sales": 150, "prev_sales": 100}, {"month": 3, "sales": 200, "prev_sales": 150}]

B [{"month": 1, "sales": 100, "prev_sales": null}, {"month": 2, "sales": 150, "prev_sales": 100}, {"month": 3, "sales": 200, "prev_sales": 150}]

C [{"month": 1, "sales": 100, "prev_sales": 100}, {"month": 2, "sales": 150, "prev_sales": 150}, {"month": 3, "sales": 200, "prev_sales": 200}]

D [{"month": 1, "sales": 100, "prev_sales": 100}, {"month": 2, "sales": 150, "prev_sales": null}, {"month": 3, "sales": 200, "prev_sales": 150}]

💡 Hint

The third argument in LAG is the default value used when there is no previous row.

Since the first row has no previous row, LAG returns the default value 0 instead of null.

📝 Syntax

advanced

Identify the syntax error in LAG usage

Which option contains a syntax error in the use of the LAG function?

SELECT id, value, LAG(value 1) OVER (ORDER BY id) AS prev_val FROM data;

SQL

CREATE TABLE data (id INT, value INT);
INSERT INTO data VALUES (1, 10), (2, 20);

SELECT id, value, LAG(value 1) OVER (ORDER BY id) AS prev_val FROM data;

A LAG(value, 1, 0) OVER (ORDER BY id)

B LAG(value, 1) OVER (ORDER BY id)

C LAG(value 1) OVER (ORDER BY id)

D LAG(value) OVER (ORDER BY id)

💡 Hint

Check the commas between function arguments.

Option C is missing a comma between value and 1, causing a syntax error.

❓ optimization

advanced

Optimizing LAG usage with large datasets

You have a large table with millions of rows. Which approach is best to optimize the performance of a query using LAG?

A Use <code>LAG</code> with a proper <code>PARTITION BY</code> and <code>ORDER BY</code> to limit the window size.

B Replace <code>LAG</code> with a self-join on the table without indexes.

C Use <code>LAG</code> without <code>PARTITION BY</code> to process all rows at once.

D Remove the <code>ORDER BY</code> clause inside the <code>OVER</code> clause to speed up the query.

💡 Hint

Think about how window functions process data partitions.

Using PARTITION BY and ORDER BY properly limits the rows each window function processes, improving performance.

🔧 Debug

expert

Debugging unexpected NULLs in LAG output

A query uses LAG(sales) OVER (ORDER BY month) but unexpectedly returns NULL for some rows that are not the first row. What is the most likely cause?

A The query is missing a <code>PARTITION BY</code> clause.

B The <code>LAG</code> function is missing the default value argument.

C The table has NULL values in the <code>sales</code> column.

D The <code>month</code> column has duplicate values causing ties in ordering.

💡 Hint

Consider how ordering affects which row is considered previous.

If month has duplicates, the order of rows with the same month is not guaranteed, so LAG may return NULL unexpectedly.

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