SQL Window Functions Explained

Window functions are powerful SQL features that perform calculations across rows related to the current row. Unlike aggregate functions, they don’t group rows into a single output row.

Basic Syntax

function_name() OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column1, column2, ...]
    [ROWS/RANGE window_frame]
)

Common Window Functions

1. ROW_NUMBER()

Assigns unique sequential integers to rows:

SELECT 
    employee_id,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;

2. RANK() and DENSE_RANK()

Assign ranks with different tie handling:

SELECT 
    employee_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) as rank_with_gaps,
    DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

3. LAG() and LEAD()

Access previous or next row values:

SELECT 
    order_date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY order_date) as previous_revenue,
    LEAD(revenue, 1) OVER (ORDER BY order_date) as next_revenue
FROM daily_sales;

Practical Examples

Running Totals

SELECT 
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY order_date 
        ROWS UNBOUNDED PRECEDING
    ) as running_total
FROM daily_sales;

Moving Averages

SELECT 
    order_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as seven_day_avg
FROM daily_sales;

Percentiles

SELECT 
    employee_id,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) as percentile_rank,
    NTILE(4) OVER (ORDER BY salary) as quartile
FROM employees;

Performance Tips

1. Index ORDER BY columns for better performance
2. Use PARTITION BY to limit window scope
3. Consider materializing complex window calculations
4. Avoid unnecessary sorting in the window frame

Window functions are essential for advanced analytics and can replace complex self-joins in many scenarios.