SQL Window Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE, and Positional Analytics/Compare current value to final partition value
Measure remaining distance to the last value.
Section: Derived positional patterns
Compare current value to final partition value
sql
sql
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;Explanation
A neat way to compare present progress to the partition endpoint.
Learn the surrounding workflow
Compare similar commands or jump into common fixes when this command is part of a bigger troubleshooting path.
Related commands
Same sheet · prioritizing Derived positional patterns
Get the true last value in each partition
Use an explicit full frame with LAST_VALUE.
Get the first value in each partition
Carry the earliest value across all rows in the partition.