Positional window functions for first, last, and nth values inside ordered partitions.
These functions depend heavily on frame semantics.
SELECT customer_id,
order_date,
amount,
FIRST_VALUE(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS first_order_amount
FROM orders;Using the full-partition frame makes intent explicit and avoids surprises.
SELECT customer_id,
order_date,
amount,
LAST_VALUE(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_order_amount
FROM orders;Without the full frame, `LAST_VALUE` often returns the current row's value because of the default frame.
SELECT customer_id,
order_date,
amount,
NTH_VALUE(amount, 2) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS second_order_amount
FROM orders;`NTH_VALUE` is ideal for milestone-style questions such as second purchase or third touch.
Use positional functions to compute richer business metrics.
SELECT customer_id,
order_date,
amount,
amount - FIRST_VALUE(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS delta_from_first
FROM orders;This shows how far each later value has drifted from the starting point.
SELECT sprint_id,
day,
completed_points,
LAST_VALUE(completed_points) OVER (
PARTITION BY sprint_id
ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) - completed_points AS points_remaining_to_final
FROM sprint_burndown;A neat way to compare present progress to the partition endpoint.