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.