Window functions perform calculations across rows related to current row. Essential for rankings, running totals, and comparisons.

Basic Window Function

SELECT

name,

department,

salary,

AVG(salary) OVER (PARTITION BY department) as dept_avg

FROM employees;

ROW_NUMBER

SELECT

name,

salary,

ROW_NUMBER() OVER (ORDER BY salary DESC) as rank

FROM employees;

RANK and DENSE_RANK

SELECT

name,

sales,

RANK() OVER (ORDER BY sales DESC) as rank,

DENSE_RANK() OVER (ORDER BY sales DESC) as dense_rank

FROM salespeople;

Running Total

SELECT

order_date,

amount,

SUM(amount) OVER (ORDER BY order_date) as running_total

FROM orders;

LEAD and LAG

SELECT

date,

value,

LAG(value) OVER (ORDER BY date) as prev_value,

LEAD(value) OVER (ORDER BY date) as next_value

FROM daily_metrics;

Partition by Multiple Columns

SELECT

department,

employee_name,

salary,

RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank

FROM employees;

Generate Window Functions

AI2sql creates window functions for analytics.