SQL Window Functions Explained With Examples

SQL Window Functions Explained With Examples

A window function performs a calculation across a set of rows related to the current row, called its window, without collapsing those rows into one the way GROUP BY does. You write it with an OVER (...) clause: PARTITION BY splits rows into groups, and ORDER BY sets the order within each group. Window functions handle rankings, running totals, period-over-period changes and moving averages.

Window functions are what separate "knows SQL" from "fluent in SQL". They appear in most intermediate analyst interviews, and they replace a lot of awkward self-joins and subqueries. This guide uses small tables so you can check every result by hand.

GROUP BY vs Window Functions

Monthly revenue by region:

region month revenue
North 2026-01 100
North 2026-02 120
North 2026-03 90
South 2026-01 80
South 2026-02 80
South 2026-03 110

GROUP BY region gives two rows: North 310, South 270. The monthly detail is gone.

A window function keeps all six rows and adds the region total beside each one:

SELECT region, month, revenue,
       SUM(revenue) OVER (PARTITION BY region) AS region_total
FROM monthly_sales;

Each North row shows 310 and each South row shows 270. Now you can calculate each month's share of its region's total in the same query.

The Anatomy of OVER()

function(...) OVER (
    PARTITION BY ...   -- which rows belong together (optional)
    ORDER BY ...       -- order within each partition (optional)
    ROWS BETWEEN ...   -- which rows around the current one count (optional)
)

Running Totals

SELECT region, month, revenue,
       SUM(revenue) OVER (PARTITION BY region ORDER BY month) AS running_total
FROM monthly_sales;
region month revenue running_total
North 2026-01 100 100
North 2026-02 120 220
North 2026-03 90 310
South 2026-01 80 80
South 2026-02 80 160
South 2026-03 110 270

Adding ORDER BY inside OVER turns a group total into a cumulative one. PARTITION BY restarts it for each region.

Period-over-Period With LAG and LEAD

LAG looks at a previous row, and LEAD looks at a following one:

SELECT region, month, revenue,
       LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev_revenue,
       revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS change
FROM monthly_sales;

For North: February's change is +20 (120 vs 100) and March's is −30 (90 vs 120), a 25% drop. For South: February 0, March +30 (+37.5%). The first month in each region has no previous row, so LAG returns NULL.

This is the standard way to answer "month-over-month growth" questions. It's also a very common interview task.

Ranking: ROW_NUMBER vs RANK vs DENSE_RANK

The three ranking functions differ only in how they treat ties. Product sales:

product sales ROW_NUMBER RANK DENSE_RANK
A 500 1 1 1
B 400 2 2 2
C 400 3 2 2
D 300 4 4 3
SELECT product, sales,
       ROW_NUMBER() OVER (ORDER BY sales DESC) AS row_num,
       RANK()       OVER (ORDER BY sales DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_rnk
FROM product_sales;
  • ROW_NUMBER: always unique. Ties are broken arbitrarily, so add a second sort column if order matters.
  • RANK: ties share a rank, and the next rank skips (1, 2, 2, 4), like a sports podium.
  • DENSE_RANK: ties share a rank with no gaps (1, 2, 2, 3).

Top-N per Group

"The top 3 products in each category" is one of the most common real requests. Window functions can't go directly in WHERE, so rank first in a CTE, then filter:

WITH ranked AS (
    SELECT category, product, sales,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
    FROM product_sales
)
SELECT category, product, sales
FROM ranked
WHERE rn <= 3;

Some databases (Snowflake, BigQuery, DuckDB, Databricks) also support QUALIFY, which filters on window results directly: QUALIFY ROW_NUMBER() OVER (...) <= 3.

Moving Averages With a Frame

SELECT region, month, revenue,
       AVG(revenue) OVER (
           PARTITION BY region ORDER BY month
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3m
FROM monthly_sales;

For North in March: (100 + 120 + 90) ÷ 3 ≈ 103.3. For the first two months the window holds fewer than three rows, so check whether your report should show those partial averages.

Other Useful Window Functions

  • NTILE(4): splits rows into quartiles, for example to segment customers by spend.
  • FIRST_VALUE / LAST_VALUE: the first or last value in the window. Mind the frame, as explained below.
  • PERCENT_RANK / CUME_DIST: relative position as a fraction.
  • COUNT(*) OVER (PARTITION BY ...): group sizes shown on every row.

Common Mistakes

  1. Using a window function in WHERE. It's evaluated after WHERE, so wrap it in a CTE or subquery, or use QUALIFY where it's supported.
  2. Forgetting PARTITION BY. Then the running total runs across all regions instead of restarting for each one.
  3. The default frame surprise. With ORDER BY and no explicit frame, many databases use RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Rows with tied order values are then treated together, and LAST_VALUE returns the current row instead of the last one. Write ROWS BETWEEN ... explicitly when it matters.
  4. ROW_NUMBER on ties without a tie-breaker. Results can change between runs. Add a second ORDER BY column.
  5. Missing periods. LAG compares with the previous row, not the previous month. If a month is missing, you'll compare March with January without noticing.

Window Functions and DAX

If you use Power BI, you'll notice similar ideas: running totals and "previous period" measures in DAX play the same role that window functions play in SQL. See What Is DAX?.

What to Learn Next

Window functions are Stage 4 of our SQL roadmap, after joins and CTEs. Practice them with the window-function questions in our 50 SQL interview questions. For structured learning, SQL for Data Science (UC Davis) builds toward analytical SQL, and Introduction to SQL (DataCamp) is a good place to start if you're still on the basics. Browse all Data Analysis courses.

Frequently Asked Questions

What's the difference between GROUP BY and PARTITION BY? GROUP BY collapses each group into one row. PARTITION BY (inside OVER) defines groups for a calculation but keeps every row.

Are window functions supported in MySQL? Yes, from MySQL 8.0. PostgreSQL, SQL Server, Oracle, SQLite (3.25+), BigQuery, Snowflake and other major databases also support them.

Are window functions slow? They're usually efficient, and often faster than the self-joins they replace. On very large tables, partitioning and sorting cost time, so filter data early.

What's the difference between RANK and DENSE_RANK? Both give tied rows the same rank. RANK skips the following numbers (1, 2, 2, 4), while DENSE_RANK doesn't (1, 2, 2, 3).

Bottom Line

Window functions calculate across related rows without collapsing them: running totals with SUM() OVER (ORDER BY), period changes with LAG, rankings with ROW_NUMBER, RANK and DENSE_RANK, top-N with a CTE, and moving averages with an explicit ROWS frame. Learn these five patterns and you'll handle most analytical SQL you meet at work or in interviews.

Enjoyed this article?

Share it with your network

Listings related to SQL Window Functions Explained With Examples