SQL Window Functions: The Secret Weapon for Analytics

Unlock answers that GROUP BY can't give you

Window functions let you calculate across rows without collapsing them. They're the most powerful yet underused feature in SQL.

Running totals

SELECT date, revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total
FROM sales;

Rank within groups

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

Moving averages

SELECT date, revenue,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS weekly_avg
FROM sales;

Key functions to master: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), NTILE().

Once you learn window functions, you'll wonder how you ever wrote reports without them.