What Are Window Functions?

Window functions operate on a "window" of rows defined by the OVER() clause. They are essential for tasks like ranking, running totals, moving averages, and comparing rows without using subqueries or self-joins.

Window Functions vs Aggregate Functions

Syntax Overview

-- Basic window function structure
SELECT
    employee_name,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total_salary,
    salary - SUM(salary) OVER (PARTITION BY department) AS diff_from_dept_total
FROM employees;

-- Using multiple window functions in one query
SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

Window Function Categories

Practical Example: Salary Analysis

-- Comprehensive employee salary analysis using window functions
SELECT
    e.employee_name,
    e.department,
    e.salary,
    -- Rank employees within their department
    RANK() OVER (
        PARTITION BY e.department
        ORDER BY e.salary DESC
    ) AS dept_salary_rank,
    -- Compare to department average
    ROUND(e.salary - AVG(e.salary) OVER (PARTITION BY e.department), 2) AS vs_dept_avg,
    -- Running total of salaries in department
    SUM(e.salary) OVER (
        PARTITION BY e.department
        ORDER BY e.salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_dept_total,
    -- Percentage of department total
    ROUND(e.salary / SUM(e.salary) OVER (PARTITION BY e.department) * 100, 1) AS pct_of_dept
FROM employees e
ORDER BY e.department, dept_salary_rank;

Aggregate Window Functions

-- Running total and moving average
SELECT
    order_date,
    order_amount,
    SUM(order_amount) OVER (ORDER BY order_date) AS running_total,
    AVG(order_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3,
    COUNT(*) OVER (ORDER BY order_date) AS cumulative_count
FROM orders;

Quick Reference Table