SQL Window Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE, and Positional Analytics

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;

# Carry the earliest value across all rows in the partition.

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;

# Use an explicit full frame with LAST_VALUE.

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;

# Pull a specific positional value.

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;

# Compare each row to the partition's first value.

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;

# Measure remaining distance to the last value.