SQL window functions: running totals, ranks and month-over-month

SUM(revenue) OVER (ORDER BY month) gives a running total. RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) ranks within each department.

LAG(revenue) OVER (ORDER BY month) returns last month's value on the same row, which makes growth calculations simple.

Worked example

Rank employees by salary within their department.

SELECT name, department_id, salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees
ORDER BY department_id, salary_rank
LIMIT 8;

Common mistakes

• Filtering on a window result in WHERE: wrap the query in a CTE first.
• Leaving out PARTITION BY and ranking across the whole company.