FIRST_VALUE and LAST_VALUE in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of FIRST_VALUE with window frame

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

SELECT region, month, revenue,
       FIRST_VALUE(revenue) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_rev
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),
('East', 3, 120),
('West', 1, 200),
('West', 2, 180);

SELECT region, month, revenue,
       FIRST_VALUE(revenue) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_rev
FROM sales
ORDER BY region, month;

A [{"region": "East", "month": 1, "revenue": 100, "first_rev": 100}, {"region": "East", "month": 2, "revenue": 150, "first_rev": 100}, {"region": "East", "month": 3, "revenue": 120, "first_rev": 100}, {"region": "West", "month": 1, "revenue": 200, "first_rev": 200}, {"region": "West", "month": 2, "revenue": 180, "first_rev": 200}]

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

C [{"region": "East", "month": 1, "revenue": 100, "first_rev": 100}, {"region": "East", "month": 2, "revenue": 150, "first_rev": 150}, {"region": "East", "month": 3, "revenue": 120, "first_rev": 150}, {"region": "West", "month": 1, "revenue": 200, "first_rev": 200}, {"region": "West", "month": 2, "revenue": 180, "first_rev": 200}]

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

💡 Hint

Remember that FIRST_VALUE returns the first value in the window frame defined by the ORDER BY and ROWS clause.

The window frame is from the start (UNBOUNDED PRECEDING) to the current row, so FIRST_VALUE always returns the revenue of the first month in each region partition.

❓ query_result

intermediate

Output of LAST_VALUE with default frame

Consider the table orders with columns customer, order_date, and amount. What is the output of this query?

SELECT customer, order_date, amount,
       LAST_VALUE(amount) OVER (PARTITION BY customer ORDER BY order_date) AS last_amt
FROM orders
ORDER BY customer, order_date;

PostgreSQL

CREATE TABLE orders (customer TEXT, order_date DATE, amount INT);
INSERT INTO orders VALUES
('Alice', '2024-01-01', 50),
('Alice', '2024-01-05', 70),
('Bob', '2024-01-02', 30),
('Bob', '2024-01-03', 40);

SELECT customer, order_date, amount,
       LAST_VALUE(amount) OVER (PARTITION BY customer ORDER BY order_date) AS last_amt
FROM orders
ORDER BY customer, order_date;

A [{"customer": "Alice", "order_date": "2024-01-01", "amount": 50, "last_amt": 50}, {"customer": "Alice", "order_date": "2024-01-05", "amount": 70, "last_amt": 50}, {"customer": "Bob", "order_date": "2024-01-02", "amount": 30, "last_amt": 30}, {"customer": "Bob", "order_date": "2024-01-03", "amount": 40, "last_amt": 30}]

B [{"customer": "Alice", "order_date": "2024-01-01", "amount": 50, "last_amt": 70}, {"customer": "Alice", "order_date": "2024-01-05", "amount": 70, "last_amt": 70}, {"customer": "Bob", "order_date": "2024-01-02", "amount": 30, "last_amt": 40}, {"customer": "Bob", "order_date": "2024-01-03", "amount": 40, "last_amt": 40}]

C [{"customer": "Alice", "order_date": "2024-01-01", "amount": 50, "last_amt": null}, {"customer": "Alice", "order_date": "2024-01-05", "amount": 70, "last_amt": null}, {"customer": "Bob", "order_date": "2024-01-02", "amount": 30, "last_amt": null}, {"customer": "Bob", "order_date": "2024-01-03", "amount": 40, "last_amt": null}]

D [{"customer": "Alice", "order_date": "2024-01-01", "amount": 50, "last_amt": 50}, {"customer": "Alice", "order_date": "2024-01-05", "amount": 70, "last_amt": 70}, {"customer": "Bob", "order_date": "2024-01-02", "amount": 30, "last_amt": 30}, {"customer": "Bob", "order_date": "2024-01-03", "amount": 40, "last_amt": 40}]

💡 Hint

LAST_VALUE by default uses the window frame from the first row to the current row, so it returns the last value in that frame, which is the current row's value.

Without specifying ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, LAST_VALUE returns the value of the current row, not the last in the partition.

📝 Syntax

advanced

Identify the syntax error in FIRST_VALUE usage

Which option contains a syntax error when using FIRST_VALUE in PostgreSQL?

A SELECT FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM employees;

B SELECT FIRST_VALUE(salary) FROM employees ORDER BY department;

C SELECT FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) FROM employees;

D SELECT FIRST_VALUE(salary) OVER (ORDER BY department) FROM employees;

💡 Hint

Remember that FIRST_VALUE is a window function and requires an OVER clause.

Option B lacks the OVER clause, so it is a syntax error. FIRST_VALUE must be used with OVER to define the window.

❓ optimization

advanced

Optimizing LAST_VALUE for full partition

You want to get the last sale amount per customer over all their orders. Which query correctly uses LAST_VALUE to always return the last amount in the entire partition regardless of row position?

A SELECT customer, order_date, amount, LAST_VALUE(amount) OVER (PARTITION BY customer ORDER BY order_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS last_amt FROM sales;

B SELECT customer, order_date, amount, LAST_VALUE(amount) OVER (PARTITION BY customer ORDER BY order_date) AS last_amt FROM sales;

C SELECT customer, order_date, amount, LAST_VALUE(amount) OVER (PARTITION BY customer ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amt FROM sales;

D SELECT customer, order_date, amount, LAST_VALUE(amount) OVER (PARTITION BY customer ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_amt FROM sales;

💡 Hint

To get the last value in the entire partition, the window frame must cover all rows.

Option C sets the frame from the first to the last row in the partition, so LAST_VALUE returns the last amount overall.

🧠 Conceptual

expert

Understanding window frame impact on FIRST_VALUE and LAST_VALUE

Which statement best explains why the output of FIRST_VALUE and LAST_VALUE can differ depending on the window frame specified?

A Because the window frame defines the subset of rows considered for each row, changing which values are first or last in that frame.

B Because FIRST_VALUE and LAST_VALUE always return the first and last values of the entire table regardless of window frame.

C Because FIRST_VALUE ignores the ORDER BY clause while LAST_VALUE uses it to find the last value.

D Because window frames only affect aggregate functions, not FIRST_VALUE or LAST_VALUE.

💡 Hint

Think about how the window frame limits the rows the function looks at for each row.

The window frame restricts the rows considered for each row, so FIRST_VALUE and LAST_VALUE return values based on that subset, not the entire partition.