Window functions: what problem do they solve? Give a quick example.

Answer

Window functions compute values over a “window” of rows while keeping each row in the result (unlike GROUP BY which collapses rows). They’re great for rankings, running totals, and “top N per group”.

SELECT user_id,
       created_at,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM logins;