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.

Practice

1. What does the SQL function FIRST_VALUE(column_name) OVER (ORDER BY column_name) return?

easy

A. The average value of the column

B. The first value in the ordered set of rows for that column

C. The last value in the ordered set of rows for that column

D. The total count of rows in the table

Solution

1. Step 1: Understand FIRST_VALUE function

   It returns the first value in the ordered set of rows defined by the OVER clause.

2. Step 2: Analyze the ORDER BY clause

   The ORDER BY inside OVER() defines the order to pick the first value from.

3. Final Answer:

   The first value in the ordered set of rows for that column -> Option B

4. Quick Check:

   FIRST_VALUE returns first row value [OK]

Hint: FIRST_VALUE picks the first row after ordering [OK]

Common Mistakes:

• Confusing FIRST_VALUE with LAST_VALUE
• Thinking it returns an aggregate like average
• Ignoring the ORDER BY inside OVER()

2. Which of the following is the correct syntax to get the last value of salary ordered by hire_date in SQL?

easy

A. SELECT LAST_VALUE(salary) OVER (ORDER BY hire_date) FROM employees;

B. SELECT LAST_VALUE(salary) FROM employees ORDER BY hire_date;

C. SELECT LAST_VALUE(salary) OVER (PARTITION BY hire_date) FROM employees;

D. SELECT LAST_VALUE(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM employees;

Solution

1. Step 1: Understand LAST_VALUE default frame

   LAST_VALUE by default looks only at rows up to current row, so it may not return the true last value.

2. Step 2: Use frame clause to include all rows

   Adding ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ensures LAST_VALUE looks at the entire partition.

3. Final Answer:

   SELECT LAST_VALUE(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM employees; -> Option D

4. Quick Check:

   LAST_VALUE needs full frame to get last row [OK]

Hint: Add full frame clause to LAST_VALUE for correct last row [OK]

Common Mistakes:

• Omitting frame clause with LAST_VALUE
• Using PARTITION BY incorrectly
• Trying to use LAST_VALUE without OVER()

3. Given the table sales with columns region and amount, what will this query return?

SELECT region, amount, FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY amount DESC) AS max_amount FROM sales;

medium

A. The first amount in the whole table regardless of region

B. The lowest amount per region repeated on each row of that region

C. The highest amount per region repeated on each row of that region

D. Syntax error due to missing frame clause

Solution

1. Step 1: Analyze PARTITION BY and ORDER BY

   The window partitions rows by region and orders amounts descending within each region.

2. Step 2: Understand FIRST_VALUE with ordering

   FIRST_VALUE picks the first row's amount in each partition ordered by amount DESC, which is the max amount.

3. Final Answer:

   The highest amount per region repeated on each row of that region -> Option C

4. Quick Check:

   FIRST_VALUE with DESC order gives max per partition [OK]

Hint: Use ORDER BY DESC with FIRST_VALUE for max per group [OK]

Common Mistakes:

• Assuming FIRST_VALUE always returns lowest value
• Confusing PARTITION BY with filtering
• Expecting syntax error without frame clause here

4. Identify the error in this query:

SELECT employee_id, LAST_VALUE(salary) OVER (ORDER BY employee_id) AS last_sal FROM employees;

medium

A. LAST_VALUE needs a frame clause to get correct last value

B. Missing PARTITION BY clause causes error

C. ORDER BY cannot be used inside OVER()

D. salary column must be aggregated with GROUP BY

Solution

1. Step 1: Check LAST_VALUE default behavior

   By default, LAST_VALUE looks only up to current row, so it may not return the true last value.

2. Step 2: Add frame clause to fix

   Adding ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING makes LAST_VALUE consider all rows.

3. Final Answer:

   LAST_VALUE needs a frame clause to get correct last value -> Option A

4. Quick Check:

   LAST_VALUE without frame returns wrong last row [OK]

Hint: Add full frame to LAST_VALUE for correct last row [OK]

Common Mistakes:

• Thinking PARTITION BY is mandatory
• Believing ORDER BY is invalid inside OVER()
• Confusing LAST_VALUE with aggregate functions

5. You want to find the first and last sale amounts per customer ordered by sale date from the sales table. Which query correctly returns both values on each row?

hard

A. SELECT customer_id, sale_date, FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY sale_date) AS first_sale, LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale FROM sales;

B. SELECT customer_id, sale_date, FIRST_VALUE(amount) OVER (ORDER BY sale_date) AS first_sale, LAST_VALUE(amount) OVER (ORDER BY sale_date) AS last_sale FROM sales;

C. SELECT customer_id, sale_date, FIRST_VALUE(amount) AS first_sale, LAST_VALUE(amount) AS last_sale FROM sales GROUP BY customer_id;

D. SELECT customer_id, sale_date, MIN(amount) OVER (PARTITION BY customer_id) AS first_sale, MAX(amount) OVER (PARTITION BY customer_id) AS last_sale FROM sales;

Solution

1. Step 1: Use PARTITION BY customer_id

   This groups rows by customer to get first and last sales per customer.

2. Step 2: Use ORDER BY sale_date for chronological order

   Ordering by sale_date ensures first and last are by date.

3. Step 3: Add frame clause for LAST_VALUE

   LAST_VALUE needs ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get true last value.

4. Final Answer:

   Query in option A correctly returns first and last sale amounts per customer -> Option A

5. Quick Check:

   FIRST_VALUE and LAST_VALUE with proper frame and partition [OK]

Hint: Use full frame for LAST_VALUE with PARTITION and ORDER BY [OK]

Common Mistakes:

• Omitting frame clause for LAST_VALUE
• Using aggregate functions instead of window functions
• Not partitioning by customer_id