Return the first or last value within a window. Useful for "compare each row to the partition's first/last value" — for example, "how much have we grown since the user's first order?"
SQL
-- Each user's spend on every order + their first-ever order amount
SELECT user_id,
created_at,
amount,
FIRST_VALUE(amount) OVER (
PARTITION BY user_id
ORDER BY created_at
) AS first_order_amount,
amount - FIRST_VALUE(amount) OVER (
PARTITION BY user_id ORDER BY created_at
) AS growth_from_first
FROM orders;
-- LAST_VALUE needs an explicit frame to look across the WHOLE partition
SELECT user_id, created_at, amount,
LAST_VALUE(amount) OVER (
PARTITION BY user_id
ORDER BY created_at
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_order_amount
FROM orders;Save your own code snippets
Create a free account and build your private vault. Share publicly whenever you want.