Lessons › SQL › Deep Dive: Advanced Window Functions

SQL lesson

FIRST_VALUE() / LAST_VALUE() / NTH_VALUE()

These window functions (meaning they look across a set of related rows without collapsing them into one) let you reach into a ranked list and pull the value at a specific position - first, last, or anywhere in between - and stamp it alongside every row. Like bookmarking the opening chapter, the final page, and chapter two of every reader's history so you can compare them all at a glance.

Example

Navigating Customer Order Values with FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE()

SELECT order_id, customer_id, order_date, revenue, FIRST_VALUE(revenue) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_order_revenue, LAST_VALUE(revenue) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order_revenue, NTH_VALUE(revenue, 2) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_order_revenue FROM orders ORDER BY customer_id, order_date

Result:

Step by step

1. AS gives a column a friendlier name in the result.

   SELECT order_id, customer_id, order_date, revenue, FIRST_VALUE(revenue) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_order_revenue, LAST_VALUE(revenue) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order_revenue, NTH_VALUE(revenue, 2) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_order_revenue

2. FROM tells SQL which table to pull the data from.

   SELECT order_id, customer_id, order_date, revenue, FIRST_VALUE(revenue) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_order_revenue, LAST_VALUE(revenue) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order_revenue, NTH_VALUE(revenue, 2) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_order_revenue
   FROM orders

3. ORDER BY puts the rows in your chosen order.

   SELECT order_id, customer_id, order_date, revenue, FIRST_VALUE(revenue) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_order_revenue, LAST_VALUE(revenue) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order_revenue, NTH_VALUE(revenue, 2) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_order_revenue
   FROM orders
   ORDER BY customer_id, order_date;

Practice

Try each one in your head first, then open the answer.

SELECT *, FIRST_VALUE(revenue) OVER (PARTITION BY category ORDER BY revenue) FROM orders

SELECT *, LAST_VALUE(revenue) OVER (PARTITION BY sale_id ORDER BY revenue) FROM store_sales

SELECT *, NTH_VALUE(price, 3) OVER (PARTITION BY category ORDER BY price) FROM products

SELECT *, FIRST_VALUE(salary) OVER (PARTITION BY full_name ORDER BY salary) FROM employees

SELECT *, LAST_VALUE(rating) OVER (PARTITION BY review_id ORDER BY rating) FROM reviews