GROUP BY's clever cousin
GROUP BY squashes rows into one per group. Window functions compute across related rows but keep every row: rank each sale within its branch, show a running total, compare with yesterday. They're one of SQL's most powerful tools.
OVER (PARTITION BY … ORDER BY …)
SELECT branch, staff, sold_on, amount,
sum(amount) OVER (PARTITION BY branch) AS branch_total,
rank() OVER (PARTITION BY branch ORDER BY amount DESC) AS rank_in_branch
FROM sales
ORDER BY branch, rank_in_branch;
SELECT sold_on, sum(amount) AS day_total,
sum(sum(amount)) OVER (ORDER BY sold_on) AS running_total
FROM sales GROUP BY sold_on ORDER BY sold_on;
SELECT staff, sold_on, amount,
amount - lag(amount) OVER (PARTITION BY staff ORDER BY sold_on) AS change
FROM sales ORDER BY staff, sold_on;