Window Function

ELI5 — The Vibe Check

Window functions let you do calculations across related rows without collapsing them into one result like GROUP BY does. Want to rank users by score, calculate running totals, or compare each row to the previous one? Window functions. They're the 'I want my cake and eat it too' of SQL.

Real Talk

A window function performs a calculation across a set of rows (the 'window') related to the current row, without reducing the number of rows returned. Common functions include ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() OVER(), and NTILE(). The PARTITION BY clause defines groups, and ORDER BY defines row ordering within each partition.

Show Me The Code

SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  salary - LAG(salary) OVER (ORDER BY salary) AS diff_from_prev
FROM employees;

When You'll Hear This

"Window functions let you rank without losing individual rows." / "Use ROW_NUMBER() OVER() when you need to deduplicate results."