SQL Window Functions: Complete Guide with Real Examples (2026)

SQL window functions are the most powerful tool a data analyst can add to their toolkit. They let you calculate rankings, running totals, and comparisons to adjacent rows — without collapsing results like GROUP BY does. This guide covers every function you need.

What are Window Functions?

A window function performs a calculation across a set of rows related to the current row while keeping all rows intact. Unlike GROUP BY, every row keeps its own result — plus the window calculation alongside it.

Syntax

SELECT
    column1,
    WINDOW_FUNCTION() OVER (
        PARTITION BY group_column   -- reset calculation per group
        ORDER BY sort_column        -- order within the window
    ) AS alias
FROM table;

Ranking Functions

SELECT name, department, salary,
    ROW_NUMBER()  OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()        OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
    DENSE_RANK()  OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk,
    NTILE(4)      OVER (ORDER BY salary) AS salary_quartile
FROM employees;

Top N Per Group (Most Common Interview Question)

-- Top 3 earners per department
SELECT * FROM (
    SELECT name, department, salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
) t WHERE rnk <= 3;

LAG and LEAD — Compare to Previous/Next Row

-- Month-over-month revenue change
SELECT month, revenue,
    LAG(revenue)  OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS change,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
          / LAG(revenue) OVER (ORDER BY month), 2) AS pct_change
FROM monthly_sales;

Running Totals and Moving Averages

SELECT date, daily_sales,
    SUM(daily_sales) OVER (ORDER BY date) AS running_total,
    AVG(daily_sales) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7day
FROM daily_sales;

Year-to-Date (Resets Each Year)

SELECT date, daily_sales,
    SUM(daily_sales) OVER (
        PARTITION BY YEAR(date)
        ORDER BY date
    ) AS ytd_sales
FROM daily_sales;

Window Functions vs GROUP BY

FAQ

Do window functions work in MySQL?

Yes, since MySQL 8.0. All major databases — PostgreSQL, SQL Server, BigQuery, Snowflake, Redshift — fully support them.

What is ROWS BETWEEN vs RANGE BETWEEN?

ROWS operates on physical row positions. RANGE operates on logical values — rows with the same ORDER BY value are all included. For most use cases, ROWS BETWEEN is what you want.