FIRST_VALUE and LAST_VALUE in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the FIRST_VALUE function do in SQL?

FIRST_VALUE returns the first value in an ordered set of rows within a window.

It helps you get the earliest or smallest value based on the order you define.

beginner

What is the purpose of the LAST_VALUE function in SQL?

LAST_VALUE returns the last value in an ordered set of rows within a window.

It helps you get the latest or largest value based on the order you define.

intermediate

How do FIRST_VALUE and LAST_VALUE differ from aggregate functions like MIN and MAX?

FIRST_VALUE and LAST_VALUE return values based on row order within a window, not just the minimum or maximum value.

They consider the order of rows, while MIN and MAX find the smallest or largest value regardless of order.

advanced

What happens if you use LAST_VALUE without adjusting the window frame in PostgreSQL?

By default, LAST_VALUE looks at the current row and all previous rows, so it may return the current row's value instead of the last in the partition.

You often need to set the window frame to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value.

beginner

Write a simple SQL query using FIRST_VALUE to get the first sale date per customer from a sales table.

SELECT customer_id,
       sale_date,
       FIRST_VALUE(sale_date) OVER (PARTITION BY customer_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date
FROM sales;

This query shows each sale and the first sale date for that customer.

What does FIRST_VALUE return in a window function?

A The first value in the ordered window

B The smallest value in the entire table

C The last value in the ordered window

D The average value in the window

FIRST_VALUE returns the first value in the ordered set of rows defined by the window.

Which clause is essential to define the order for FIRST_VALUE and LAST_VALUE?

A GROUP BY

B ORDER BY

C WHERE

D HAVING

The ORDER BY clause inside the window function defines the order of rows.

If you want LAST_VALUE to return the last value in the entire partition, what should you do?

A Use <code>ORDER BY DESC</code> only

B Use <code>GROUP BY</code> clause

C Nothing, it works by default

D Use <code>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</code> frame

Adjusting the window frame ensures LAST_VALUE looks at all rows in the partition.

Which function would you use to get the earliest date in a group ignoring row order?

A MIN()

B FIRST_VALUE()

C LAST_VALUE()

D AVG()

MIN() returns the smallest value regardless of row order.

Can FIRST_VALUE and LAST_VALUE be used without a window function?

A Only <code>FIRST_VALUE</code> can be used without OVER()

B Yes, like aggregate functions

C No, they require an OVER() clause

D Only <code>LAST_VALUE</code> can be used without OVER()

Both require the OVER() clause to define the window.

Explain how FIRST_VALUE and LAST_VALUE work in SQL window functions.

Think about how they pick values from ordered rows in a group.

Describe a situation where you would need to adjust the window frame when using LAST_VALUE.

Consider how window frames affect which rows are visible to the function.