First & Last Value — Solution & ExplanationProblems / First & Last Value / EditorialApproachThe key challenge is LAST_VALUE. By default, a window with ORDER BY uses a frame ending at the current row, so LAST_VALUE would just return the current row's value.

Explicit frame: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING makes both functions see the entire partition.

FIRST_VALUE: Returns the amount from the earliest transaction.

LAST_VALUE: Returns the amount from the latest transaction.

This is a classic gotcha — most SQL learners expect LAST_VALUE to automatically look at the whole partition, but it respects the window frame.Hints

LAST_VALUE with the default frame only sees up to the current row — you need ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

FIRST_VALUE works correctly with the default frame, but using the full frame is cleaner.

This is one of the most common LAST_VALUE pitfalls in SQL.

Solve First & Last Value yourself →