With SUM(...) OVER (ORDER BY order_date) and no frame clause, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE works on values, not rows. All rows with the same order_date are peers, and every peer is included in the frame at once. So two orders on the same date both show the total that already includes both.
Example
order_date amount running_total (default RANGE) running_total (ROWS) 2025-01-01 10 10 10 2025-01-02 20 50 30 2025-01-02 20 50 50 2025-01-03 5 55 55
With RANGE, both Jan 2 rows show 50. With ROWS, the total grows one row at a time.
SUM(amount) OVER ( ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
Which one is "correct" depends on what you want. If the report is per day, the RANGE result is actually right at the end of each day. If you want a running total per order, use ROWS and add a tie-breaker (order_id) so the order of the same-date rows is stable. Without a tie-breaker, the ROWS result changes between runs.
The LAST_VALUE surprise
The same default frame explains why LAST_VALUE(x) OVER (ORDER BY d) looks broken. The frame stops at the current row, so the "last" value in the frame is the current row's own value. You get back the value you started with.
LAST_VALUE(x) OVER ( ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
State the full frame when you use LAST_VALUE or NTH_VALUE. Better still, write the frame clause on every running calculation so nobody has to remember the default.