-- Created on savesnippets.com · https://savesnippets.com/PVgMZTNads6VBY
-- 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;