FIRST_VALUE and LAST_VALUE in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of FIRST_VALUE in this query?

Consider a table sales with columns region, month, and revenue. The data is:

region | month | revenue
-------+-------+--------
East   | 1     | 100
East   | 2     | 150
West   | 1     | 200
West   | 2     | 180

What is the output of this query?

SELECT region, month, revenue,
       FIRST_VALUE(revenue) OVER (PARTITION BY region ORDER BY month) AS first_rev
FROM sales
ORDER BY region, month;

SQL

SELECT region, month, revenue,
       FIRST_VALUE(revenue) OVER (PARTITION BY region ORDER BY month) 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": 150}, {"region": "West", "month": 1, "revenue": 200, "first_rev": 200}, {"region": "West", "month": 2, "revenue": 180, "first_rev": 180}]

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

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

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

💡 Hint

Remember, FIRST_VALUE returns the first value in the window frame ordered by the specified column.

The FIRST_VALUE function returns the first revenue value for each region when ordered by month. So for East, the first revenue is 100 (month 1), and for West, it is 200 (month 1). This value repeats for all rows in the partition.

❓ query_result

intermediate

What does LAST_VALUE return in this query?

Using the same sales table as before, what is the output of this query?

SELECT region, month, revenue,
       LAST_VALUE(revenue) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_rev
FROM sales
ORDER BY region, month;

SQL

SELECT region, month, revenue,
       LAST_VALUE(revenue) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_rev
FROM sales
ORDER BY region, month;

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

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

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

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

💡 Hint

LAST_VALUE needs the window frame to include all rows to get the last value in the partition.

By specifying ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, the window frame covers all rows in the partition. So LAST_VALUE returns the last revenue per region ordered by month: 150 for East and 180 for West, repeated for all rows.

🧠 Conceptual

advanced

Why does LAST_VALUE sometimes return unexpected results?

When using LAST_VALUE with an ORDER BY clause in a window function, why might it return the current row's value instead of the last value in the partition?

A Because the default window frame ends at the current row, so LAST_VALUE returns the value of the current row, not the last in the partition.

B Because LAST_VALUE always ignores the ORDER BY clause and returns the first value.

C Because the partitioning is ignored when using LAST_VALUE, so it returns random values.

D Because the function requires an explicit GROUP BY clause to work correctly.

💡 Hint

Think about how the window frame affects which rows are considered.

By default, the window frame for ordered window functions is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This means LAST_VALUE only sees rows up to the current one, so it returns the current row's value, not the last in the partition. To get the true last value, you must extend the frame to include all rows.

📝 Syntax

advanced

Which query correctly uses FIRST_VALUE to get the earliest date per customer?

Given a table orders with columns customer_id and order_date, which query correctly returns each order with the earliest order date for that customer?

A SELECT customer_id, order_date, FIRST_VALUE(order_date) PARTITION BY customer_id ORDER BY order_date FROM orders;

B SELECT customer_id, order_date, FIRST_VALUE(order_date) FROM orders GROUP BY customer_id ORDER BY order_date;

C SELECT customer_id, order_date, FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order FROM orders;

D SELECT customer_id, order_date, FIRST_VALUE(order_date) OVER (ORDER BY customer_id) AS first_order FROM orders;

💡 Hint

Remember the syntax for window functions includes OVER with parentheses.

Option C correctly uses FIRST_VALUE as a window function with OVER (PARTITION BY customer_id ORDER BY order_date). Option C is invalid syntax because FIRST_VALUE requires OVER clause. Option C misses partitioning by customer. Option C has incorrect syntax missing OVER keyword.

❓ optimization

expert

Optimizing LAST_VALUE usage for large datasets

You have a large transactions table with millions of rows, partitioned by account_id and ordered by transaction_date. You want to get the last transaction amount per account efficiently using LAST_VALUE. Which approach is best?

A Use LAST_VALUE without specifying window frame, relying on default frame for performance.

B Use a subquery with GROUP BY account_id and MAX(transaction_date) joined back to get last transaction amount.

C Use LAST_VALUE with window frame <code>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</code> to get the last value per partition.

D Use FIRST_VALUE ordered by transaction_date descending with default window frame.

💡 Hint

Think about how window frames affect performance on large data.

Using LAST_VALUE with an unbounded frame can be expensive because it processes all rows in the partition for each row. Using a subquery with GROUP BY and MAX to find the last transaction date per account, then joining back to get the amount, is often more efficient on large datasets.