FIRST_VALUE, LAST_VALUE, NTH_VALUE

LAG and LEAD access rows at a fixed offset from the current row: one row back, two rows forward, and so on. But sometimes the question isn't "what came before?" but rather "what was the first value in this group?" or "what was the third?"

FIRST_VALUE, LAST_VALUE, and NTH_VALUE answer those questions. They pull a value from a specific position within the window frame, regardless of where the current row sits.