SQL Window Functions: ROW_NUMBER, RANK and Running Totals

SQL window functions calculate a value for each row using a "window" of related rows, without collapsing the rows the way GROUP BY does. They are how you rank sales reps with ROW_NUMBER, RANK and DENSE_RANK, build running totals with SUM() OVER, and compare each month with the previous one using LAG. All major databases support them, including PostgreSQL, SQL Server, MySQL 8+, SQLite 3.25+ and the cloud warehouses.

Window functions vs GROUP BY

With GROUP BY, ten rows for one region become one summary row. With a window function, all ten rows stay, and each one gets an extra column, such as its rank inside the region or the region total next to it. That makes window functions ideal for questions like "what share of the region's sales did each rep make?" where you need the detail and the total side by side. If aggregates are new to you, read GROUP BY and aggregate functions first.

The OVER clause

Every window function has an OVER (...) clause with up to three parts:

• PARTITION BY: splits rows into groups (for example, per region). The calculation restarts in each partition. Optional.
• ORDER BY: sets the order inside each partition. Required for ranking, LAG/LEAD and running totals.
• Frame (ROWS BETWEEN ...): defines exactly which rows around the current one are included, such as "the previous two rows and this one."

Ranking: ROW_NUMBER, RANK and DENSE_RANK

Take a table rep_totals with each rep's quarterly sales: Ana (East) 1,200, Mia (West) 1,200, Rosa (East) 1,000 and Luis (East) 800. Ana and Mia are tied, which is where the three ranking functions differ.

SELECT
    region,
    rep,
    total,
    ROW_NUMBER() OVER (ORDER BY total DESC) AS row_num,
    RANK()       OVER (ORDER BY total DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY total DESC) AS dense_rnk
FROM rep_totals;

• ROW_NUMBER always gives unique numbers. With ties, which row gets 1 or 2 is arbitrary unless you add a tiebreaker, such as ORDER BY total DESC, rep.
• RANK gives ties the same number and then skips (1, 1, 3), like a sports leaderboard.
• DENSE_RANK gives ties the same number without gaps (1, 1, 2).

Ranking inside each group

Add PARTITION BY to restart the ranking per region:

SELECT
    region,
    rep,
    total,
    RANK() OVER (PARTITION BY region ORDER BY total DESC) AS region_rank
FROM rep_totals;

In East, Ana is 1, Rosa 2 and Luis 3; in West, Mia is 1.

NTILE: split rows into buckets

NTILE(n) divides the ordered rows into n groups of roughly equal size and returns the group number. It is a quick way to label customers as top half vs bottom half, or to build quartiles for a segmentation report:

SELECT
    rep,
    total,
    NTILE(2) OVER (ORDER BY total DESC, rep) AS half
FROM rep_totals;

Ana and Mia land in bucket 1, and Rosa and Luis in bucket 2. When the rows don't divide evenly, the first buckets get one extra row each. Keep in mind that NTILE splits by row count, not by value, so tied values can end up in different buckets.

Top N per group and deduplication

You can't filter on a window function in WHERE, because windows are calculated after WHERE runs. Wrap the query in a CTE and filter outside. This classic pattern returns each rep's most recent month from a monthly_sales table (rep, region, month, amount):

WITH ranked AS (
    SELECT
        rep,
        month,
        amount,
        ROW_NUMBER() OVER (PARTITION BY rep ORDER BY month DESC) AS rn
    FROM monthly_sales
)
SELECT rep, month, amount
FROM ranked
WHERE rn = 1;

The same pattern removes duplicate records (keep rn = 1 per customer ID) and finds the top 3 products per category (keep rn <= 3). Learn more about this structure in CTEs and subqueries in SQL.

Running totals and moving averages

Suppose monthly_sales holds Ana's sales of 500 in July, 300 in August and 400 in September. A running total adds each month to everything before it:

SELECT
    rep,
    month,
    amount,
    SUM(amount) OVER (
        PARTITION BY rep
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM monthly_sales;

Ana's running total is 500, 800 and 1,200. It restarts for each rep because of the partition.

Why write the frame explicitly? When you use ORDER BY without a frame, most databases default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats rows with the same ORDER BY value as one block. If two rows share a date, both get the same running total. ROWS gives you a true row-by-row accumulation.

Moving average

A 3-month moving average smooths out noisy data:

AVG(amount) OVER (
    PARTITION BY rep
    ORDER BY month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3m

For Mia's months (600, 250, 350) this returns 600, 425 and 400. Note that the first two months average fewer than three values.

Percent of total

An aggregate with OVER (PARTITION BY ...) and no ORDER BY gives the group total on every row, which makes share calculations easy:

SELECT
    region,
    rep,
    total,
    ROUND(100.0 * total / SUM(total) OVER (PARTITION BY region), 1) AS pct_of_region
FROM rep_totals;

In East, Ana has 40.0%, Rosa 33.3% and Luis 26.7%. The 100.0 avoids integer division in databases like SQL Server and PostgreSQL when the columns are integers.

LAG and LEAD: compare with previous rows

LAG returns a value from an earlier row and LEAD from a later one. They are perfect for month-over-month changes:

SELECT
    rep,
    month,
    amount,
    LAG(amount) OVER (PARTITION BY rep ORDER BY month)          AS prev_month,
    amount - LAG(amount) OVER (PARTITION BY rep ORDER BY month) AS change
FROM monthly_sales;

For Ana: July has no previous month (NULL), August shows 500 and a change of -200, and September shows 300 and +100. You can pass an offset and a default, such as LAG(amount, 12, 0) to look back 12 rows (a year in monthly data, if no months are missing) and return 0 when there is none.

Next steps

Practice by rebuilding the two small tables in this guide and running every query until you can predict the output. Then use window functions on your own sales data: a leaderboard, a running total and a month-over-month change. They are also among the most common topics in SQL interview questions, so they're worth the effort. If you use Power BI, you'll find the same ideas in DAX functions like RANKX and in time-intelligence measures.

Watch: video lessons

▶

▶

Frequently asked questions