SQL Window Functions
Syntax & Frame Specification
-- Basic syntax
function_name() OVER (
[PARTITION BY col1, col2] -- optional: split into groups
[ORDER BY col3 DESC] -- optional: sort within group
[ROWS|RANGE BETWEEN ... AND ...] -- optional: define frame
)
-- Frame boundaries:
-- UNBOUNDED PRECEDING — start of partition
-- N PRECEDING — N rows/range before current
-- CURRENT ROW — current row
-- N FOLLOWING — N rows/range after current
-- UNBOUNDED FOLLOWING — end of partition
-- Default frame (when ORDER BY present): RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- Default frame (no ORDER BY): ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
-- Named window (reuse across multiple functions)
SELECT dept, salary,
SUM(salary) OVER w AS dept_sum,
AVG(salary) OVER w AS dept_avg,
COUNT(*) OVER w AS dept_count
FROM employees
WINDOW w AS (PARTITION BY dept);
Function Reference
| Function | Description | Notes |
|---|---|---|
| ROW_NUMBER() | Unique sequential integer (1, 2, 3...) | No ties — always unique |
| RANK() | Rank with gaps on ties (1, 1, 3...) | Skips numbers after ties |
| DENSE_RANK() | Rank without gaps (1, 1, 2...) | No skips |
| NTILE(n) | Divide into n buckets (1 to n) | Useful for percentiles |
| PERCENT_RANK() | Relative rank 0.0 to 1.0 | (rank-1)/(total rows-1) |
| CUME_DIST() | Cumulative distribution 0.0 to 1.0 | rows ≤ current / total rows |
| LAG(col, n, default) | Value from n rows before | n defaults to 1 |
| LEAD(col, n, default) | Value from n rows after | n defaults to 1 |
| FIRST_VALUE(col) | First value in frame | Needs explicit frame for accuracy |
| LAST_VALUE(col) | Last value in frame | Needs ROWS BETWEEN ... AND UNBOUNDED FOLLOWING |
| NTH_VALUE(col, n) | nth value in frame | Returns NULL if fewer than n rows |
| SUM/AVG/COUNT/MIN/MAX | Running/moving aggregates | Frame controls what's included |
Practical Examples
-- Top N per group (top 3 salaries per department)
WITH ranked AS (
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees
)
SELECT name, dept, salary FROM ranked WHERE rn <= 3;
-- Year-over-year change
SELECT year, revenue,
LAG(revenue) OVER (ORDER BY year) AS prev_year,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY year))
/ NULLIF(LAG(revenue) OVER (ORDER BY year), 0), 1) AS yoy_pct
FROM annual_revenue;
-- 7-day moving average
SELECT date, daily_sales,
ROUND(AVG(daily_sales) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 2) AS moving_avg_7d
FROM daily_sales;
-- Running total (reset per partition)
SELECT user_id, order_date, amount,
SUM(amount) OVER (
PARTITION BY user_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS lifetime_value
FROM orders;