FIRST_VALUE / LAST_VALUE: the default-frame trap
LAST_VALUE returns the current row unless you change the default frame (up to CURRENT ROW). The fix: extend the frame to UNBOUNDED FOLLOWING.
Prerequisites
PostgreSQL, SQL Server, MySQL 8+, Oracle
SQL
SELECT
user_id,
plan,
changed_at,
FIRST_VALUE(plan) OVER w AS first_plan,
-- Piège : sans cadre explicite, LAST_VALUE = ligne courante
LAST_VALUE(plan) OVER (
PARTITION BY user_id ORDER BY changed_at
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS current_plan
FROM subscription_history
WINDOW w AS (PARTITION BY user_id ORDER BY changed_at);Result
user_id | plan | changed_at | first_plan | current_plan
---------+----------+------------+------------+--------------
42 | free | 2025-11-02 | free | business
42 | pro | 2026-01-15 | free | business
42 | business | 2026-04-20 | free | business
57 | pro | 2026-02-01 | pro | free
57 | free | 2026-05-12 | pro | free
(5 rows)SQLFIRST_VALUELAST_VALUEWindow frame