Window Functions

1SELECT column,
2    window_function() OVER (
3        PARTITION BY partition_column
4        ORDER BY sort_column
5    )
6FROM table;

 1-- ROW_NUMBER: sequential number within partition
 2SELECT name, department,
 3    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
 4FROM employees;
 5
 6-- RANK: rank with gaps for ties
 7SELECT name, RANK() OVER (ORDER BY score DESC) FROM results;
 8
 9-- DENSE_RANK: rank without gaps
10SELECT name, DENSE_RANK() OVER (ORDER BY score DESC) FROM results;
11
12-- NTILE: distribute rows into N buckets
13SELECT name, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;

 1-- LAG: access previous row's value
 2SELECT date, amount,
 3    LAG(amount) OVER (ORDER BY date) AS prev_amount
 4FROM transactions;
 5
 6-- LEAD: access next row's value
 7SELECT date, amount,
 8    LEAD(amount) OVER (ORDER BY date) AS next_amount
 9FROM transactions;
10
11-- FIRST_VALUE / LAST_VALUE
12SELECT name, salary,
13    FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_earner
14FROM employees;

1SELECT name, department, salary,
2    SUM(salary) OVER (PARTITION BY department) AS dept_total,
3    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
4    COUNT(*) OVER (PARTITION BY department) AS dept_count
5FROM employees;