SQL Window Functions — the DE Superpower

OVER() changes the game

A window function computes a value across a set of rows related to the current row, without collapsing the result like GROUP BY does. The three you will use every week:

• ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts DESC) — deduplicate to latest event per user.
• LAG(amount, 1) OVER (PARTITION BY account ORDER BY ts) — compute deltas without a self-join.
• SUM(amount) OVER (PARTITION BY account ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) — running balance.

Window functions are the dividing line between SQL you can produce in an interview and SQL that runs a real warehouse.

GROUP BY is a blender: you drop in ten strawberries and get back one smoothie — the individual fruit is gone. A window function is a conveyor belt with a mirror: each strawberry rides past on its own, but can glance sideways at its neighbours to answer 'am I bigger than the one before me?' or 'what's the running total so far?'. The rows survive; they just gain awareness of their context. That single difference — keep every row, but compute across a related set — is what turns junior SQL into senior SQL.