LAG and LEAD for row comparison in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of LAG function with partition

Given the table sales with columns region, month, and revenue, what is the output of the following query?

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

PostgreSQL

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

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

A [{"region": "East", "month": 1, "revenue": 100, "prev_revenue": null}, {"region": "East", "month": 2, "revenue": 150, "prev_revenue": null}, {"region": "West", "month": 1, "revenue": 200, "prev_revenue": null}, {"region": "West", "month": 2, "revenue": 180, "prev_revenue": null}]

B [{"region": "East", "month": 1, "revenue": 100, "prev_revenue": 150}, {"region": "East", "month": 2, "revenue": 150, "prev_revenue": 100}, {"region": "West", "month": 1, "revenue": 200, "prev_revenue": 180}, {"region": "West", "month": 2, "revenue": 180, "prev_revenue": 200}]

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

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

💡 Hint

Think about how LAG works with partition and order.

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

❓ query_result

intermediate

Output of LEAD function without partition

Consider the table employees with columns id, name, and salary. What is the output of this query?

SELECT id, name, salary, LEAD(salary) OVER (ORDER BY id) AS next_salary FROM employees ORDER BY id;

PostgreSQL

CREATE TABLE employees (id INT, name TEXT, salary INT);
INSERT INTO employees VALUES
(1, 'Alice', 5000),
(2, 'Bob', 6000),
(3, 'Charlie', 5500);

SELECT id, name, salary, LEAD(salary) OVER (ORDER BY id) AS next_salary FROM employees ORDER BY id;

A [{"id": 1, "name": "Alice", "salary": 5000, "next_salary": 6000}, {"id": 2, "name": "Bob", "salary": 6000, "next_salary": 5500}, {"id": 3, "name": "Charlie", "salary": 5500, "next_salary": null}]

B [{"id": 1, "name": "Alice", "salary": 5000, "next_salary": 5000}, {"id": 2, "name": "Bob", "salary": 6000, "next_salary": 6000}, {"id": 3, "name": "Charlie", "salary": 5500, "next_salary": 5500}]

C [{"id": 1, "name": "Alice", "salary": 5000, "next_salary": null}, {"id": 2, "name": "Bob", "salary": 6000, "next_salary": null}, {"id": 3, "name": "Charlie", "salary": 5500, "next_salary": null}]

D [{"id": 1, "name": "Alice", "salary": 5000, "next_salary": 5500}, {"id": 2, "name": "Bob", "salary": 6000, "next_salary": null}, {"id": 3, "name": "Charlie", "salary": 5500, "next_salary": null}]

💡 Hint

LEAD returns the next row's value ordered by id.

LEAD(salary) returns the salary of the next employee ordered by id. The last row has no next row, so it returns null.

📝 Syntax

advanced

Identify the syntax error in LAG usage

Which option contains a syntax error when using the LAG function in PostgreSQL?

PostgreSQL

SELECT id, value, LAG(value, 2, 0) OVER (ORDER BY id) AS lag_val FROM data;

A SELECT id, value, LAG(value) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS lag_val FROM data;

B SELECT id, value, LAG(value 2 0) OVER (ORDER BY id) AS lag_val FROM data;

C SELECT id, value, LAG(value, 2) OVER (ORDER BY id) AS lag_val FROM data;

D SELECT id, value, LAG(value, 2, 0) OVER (ORDER BY id) AS lag_val FROM data;

💡 Hint

Check the commas between function arguments.

Option B misses commas between arguments in LAG function, causing a syntax error.

❓ optimization

advanced

Optimizing LEAD usage for large datasets

You have a large table orders with columns order_id, customer_id, and order_date. You want to find the next order date for each customer efficiently. Which query is the most optimized?

A SELECT order_id, customer_id, order_date, LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_date FROM orders;

B SELECT o1.order_id, o1.customer_id, o1.order_date, (SELECT MIN(o2.order_date) FROM orders o2 WHERE o2.customer_id = o1.customer_id AND o2.order_date > o1.order_date) AS next_order_date FROM orders o1;

C SELECT order_id, customer_id, order_date, LEAD(order_date) OVER (ORDER BY order_date) AS next_order_date FROM orders;

D SELECT order_id, customer_id, order_date, MAX(order_date) OVER (PARTITION BY customer_id) AS next_order_date FROM orders;

💡 Hint

Window functions are usually faster than correlated subqueries.

Option A uses LEAD with partition and order, which is efficient for large datasets. Option A uses a correlated subquery which is slower. Option A does not partition by customer, so results are incorrect. Option A uses MAX which does not find the next order date.

🧠 Conceptual

expert

Understanding NULL handling in LAG and LEAD

Consider a table temps with columns day and temperature. Some temperature values are NULL. What will be the result of this query?

SELECT day, temperature, LAG(temperature, 1, 20) OVER (ORDER BY day) AS prev_temp FROM temps ORDER BY day;

Specifically, what happens when the previous row's temperature is NULL?

A The LAG function skips NULL values and returns the last non-NULL temperature or the default 20 if none exists.

B The LAG function returns 20 as the default value whenever the previous temperature is NULL.

C The LAG function returns NULL because the previous temperature is NULL, ignoring the default value 20.

D The LAG function returns the actual previous temperature value, even if it is NULL, so the result is NULL in that case.

💡 Hint

Default value is used only when there is no previous row, not when previous value is NULL.

LAG returns the previous row's value as is, including NULLs. The default value is used only if there is no previous row.

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