Case Study 2 — The "Latest Status" That Was Always the Current Row

The most confusing window-function bug isn't slowness — it's a correct-looking but wrong result caused by the default frame. A query meant to show each event alongside the final status of its order instead showed each event's own status, and nobody could see why.

Background

An operations team tracked order status changes in a status_events table: (order_id, changed_at, status). They wanted a report showing every status event for an order alongside the order's latest (final) status, to see how each step compared to where the order ended up. The natural reach was LAST_VALUE:

-- "Each event with the order's final status"   ❌ returns the current row's status
SELECT order_id, changed_at, status,
       LAST_VALUE(status) OVER (PARTITION BY order_id ORDER BY changed_at) AS final_status
FROM status_events
ORDER BY order_id, changed_at;

The final_status column was wrong: instead of showing the order's last status on every row, it showed each row's own status — making final_status identical to status on every line. The report looked plausible (it had values!) but was meaningless, and the team chased it for a day, suspecting the data.

What went wrong: the default frame

The data was fine. The bug is the default window frame. When a window has an ORDER BY but no explicit frame, the frame defaults to:

   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

That is, the window for each row spans from the start of the partition up to the current row — it does not include rows after the current one. So LAST_VALUE (the last row in the frame) is, by default, the current row itself. As the query walks each event in time order, the frame grows but always ends at the current row, so "the last value so far" is whatever row you're on. That's exactly the symptom: final_status equals status on every line.

This is the single most common window-function surprise. FIRST_VALUE works "as expected" with the default frame (the first row is always in scope), which makes LAST_VALUE's behavior feel inconsistent — but both are obeying the same default-frame rule.

The fixes

1. Set an explicit full frame so the window covers the whole partition:

SELECT order_id, changed_at, status,
       LAST_VALUE(status) OVER (
           PARTITION BY order_id ORDER BY changed_at
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING   -- whole partition
       ) AS final_status
FROM status_events
ORDER BY order_id, changed_at;

Now the frame includes all rows in the partition, so LAST_VALUE returns the genuinely last status by changed_at.

2. Or sidestep LAST_VALUE entirely with FIRST_VALUE over the reverse order, which is immune to the trap:

FIRST_VALUE(status) OVER (PARTITION BY order_id ORDER BY changed_at DESC) AS final_status

Because FIRST_VALUE with the default frame returns the first row in scope, ordering descending makes "first" mean "latest." Many practitioners prefer this idiom precisely because it avoids the frame subtlety.

3. Or, if you only need the final status (not per-event), a DISTINCT ON or a ranked subquery is even simpler. The team ultimately used the FIRST_VALUE(... ORDER BY changed_at DESC) form for clarity.

The analysis

  1. The default frame is cumulative, not whole-partition. With ORDER BY in the window, the frame ends at the current row. LAST_VALUE therefore returns the current row unless you widen the frame explicitly. Memorize this; it explains a huge share of "my window function is wrong" reports.

  2. FIRST_VALUE and LAST_VALUE feel inconsistent but aren't. Both honor the default frame; it just happens to include the first row and exclude the future. Knowing why removes the surprise.

  3. A wrong-but-plausible result is the dangerous kind. The query ran and produced values, so the team blamed the data. Window-function results should be spot-checked against a hand calculation on one partition — "for order 42, is final_status really its last event?"

  4. Prefer the idiom that avoids the trap. FIRST_VALUE(x ORDER BY t DESC) for "latest" sidesteps the frame issue and reads clearly. Choosing trap-resistant patterns is a real skill.

  5. Frames are the subtle heart of window functions. Running totals, moving averages, and first/last all depend on the frame. When a windowed result is wrong, suspect the frame first.

Discussion questions

  1. Explain, using the default frame, why LAST_VALUE returned the current row's status.
  2. Why does FIRST_VALUE "work" with the default frame while LAST_VALUE doesn't?
  3. Give two correct fixes and say which you'd choose for readability.
  4. Why did the team suspect the data, and what one-partition check would have revealed the truth?
  5. ⭐ Write the "final status per order" (one row per order, not per event) three ways: LAST_VALUE + full frame, FIRST_VALUE reversed, and a ranked subquery. When is each best?