FIRST_VALUE / LAST_VALUE: la trampa del frame por defecto
LAST_VALUE devuelve la fila actual si no cambias el frame por defecto (hasta CURRENT ROW). La solución: extender el frame a UNBOUNDED FOLLOWING.
Requisitos
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);Resultado
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