SQL Window Functions Explained: ROW_NUMBER, RANK, LAG and LEAD with Examples

Why do window functions matter?

Analysts keep hitting the same wall. You write a GROUP BY to get the total revenue per region, and the detail rows disappear. You then need the detail and the total side by side, so you reach for a self-join or a messy subquery. Window functions remove that detour.

They also appear regularly in SQL interviews for analyst and data roles, usually as "find the top 3 per group" or "compare this month with last month". If you can write these four or five patterns without looking them up, you are ahead of most candidates.

What is a window function in SQL?

A window function performs a calculation across a set of rows that are related to the current row, and returns one result for every row in the input. The set of related rows is the "window".

The general shape is:

function_name(column) OVER (
    PARTITION BY group_column
    ORDER BY sort_column
    frame_clause
)

Only OVER is compulsory. The other three parts are optional and are explained in the next section.

How is a window function different from GROUP BY?

GROUP BY returns one row per group. A window function returns every original row and attaches the calculated value to each one. In the sample data used below, GROUP BY region returns 2 rows. SUM(revenue_lakh) OVER (PARTITION BY region) returns all 8 rows, with the regional total (62 for East, 73 for West) repeated on each row.

What sample data do the examples use?

Every example below uses one small table, monthly_sales, with revenue in INR lakh for two regions over four months. The numbers are made up to keep the arithmetic easy to check. You can run the code in DuckDB, SQLite 3.25 or later, PostgreSQL or MySQL 8.0 or later. If you have not picked a tool yet, read our comparison of DuckDB vs SQLite.

CREATE TABLE monthly_sales (
    region        VARCHAR(10),
    month_start   DATE,
    revenue_lakh  INTEGER
);

INSERT INTO monthly_sales VALUES
  ('East', '2026-01-01', 12), ('East', '2026-02-01', 15),
  ('East', '2026-03-01', 15), ('East', '2026-04-01', 20),
  ('West', '2026-01-01', 18), ('West', '2026-02-01', 14),
  ('West', '2026-03-01', 22), ('West', '2026-04-01', 19);

What do PARTITION BY and ORDER BY do inside OVER?

PARTITION BY splits the rows into independent groups, and ORDER BY sets the sequence of rows inside each group. The function restarts for every partition.

• PARTITION BY region means East and West are calculated separately. If you leave it out, the whole table is one partition.
• ORDER BY month_start puts the rows in time order inside each partition. Functions such as LAG, ROW_NUMBER and running totals need it, because "previous" and "running" have no meaning without an order.
• The frame clause, for example ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, narrows the window to a sliding set of rows around the current one. You need it for moving averages.

Partitions: East and West Frame at East Apr: 2 preceding to current row

Region	Month	Revenue (lakh)
East	Jan 2026	12
East	Feb 2026	15
East	Mar 2026	15
East	Apr 2026	20
West	Jan 2026	18
West	Feb 2026	14
West	Mar 2026	22
West	Apr 2026	19

How do ROW_NUMBER, RANK and DENSE_RANK differ?

All three number rows inside a partition, and they differ only in how they treat ties. ROW_NUMBER gives every row a unique number, RANK gives tied rows the same number and then skips, and DENSE_RANK gives tied rows the same number without skipping.

SELECT
    month_start,
    revenue_lakh,
    ROW_NUMBER() OVER (ORDER BY revenue_lakh DESC, month_start) AS row_num,
    RANK()       OVER (ORDER BY revenue_lakh DESC)              AS rnk,
    DENSE_RANK() OVER (ORDER BY revenue_lakh DESC)              AS dense_rnk
FROM monthly_sales
WHERE region = 'East'
ORDER BY revenue_lakh DESC, month_start;

East had 15 lakh in both February and March, which creates the tie:

Notice that RANK jumps from 2 to 4 after the tie, while DENSE_RANK goes from 2 to 3. Also note the tiebreaker month_start inside ROW_NUMBER. Without it, the database may order the two tied rows differently on different runs.

How do LAG and LEAD work?

LAG(column, n) returns the value from n rows before the current row, and LEAD(column, n) returns the value from n rows after it. If you leave out n, it defaults to 1. Both return NULL when no such row exists, though you can supply a default as the third argument.

This is the standard way to calculate a month-on-month change, and it replaces a self-join on the previous month. The query below also adds a running total, which is covered in the next section.

SELECT
    region,
    month_start,
    revenue_lakh,
    LAG(revenue_lakh) OVER (PARTITION BY region ORDER BY month_start)
        AS prev_month,
    revenue_lakh
      - LAG(revenue_lakh) OVER (PARTITION BY region ORDER BY month_start)
        AS change,
    SUM(revenue_lakh) OVER (PARTITION BY region ORDER BY month_start)
        AS running_total
FROM monthly_sales
ORDER BY region, month_start;

Each region's first row has NULL for prev_month, because PARTITION BY region restarts the window. West's January does not borrow East's April.

How do you calculate running totals and moving averages?

For a running total, use SUM(...) OVER (PARTITION BY ... ORDER BY ...). The running_total column above is exactly that: East reaches 62 lakh and West reaches 73 lakh by April.

For a moving average, add a frame clause that limits how many rows the function can see.

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

For East this gives 12.00, 13.50, 14.00 and 16.67. March is (12 + 15 + 15) / 3 = 14.00 and April is (15 + 15 + 20) / 3 = 16.67. For West it gives 18.00, 16.00, 18.00 and 18.33. The first two rows of each region average fewer than three values, because fewer rows exist. Decide whether that is acceptable for your report, or filter those rows out.

How do you get the top N rows per group?

Number the rows with ROW_NUMBER inside each group, then filter on that number in an outer query. You cannot filter on a window function in the same WHERE clause, because the database applies WHERE before it calculates the window.

WITH ranked AS (
    SELECT
        region,
        month_start,
        revenue_lakh,
        ROW_NUMBER() OVER (
            PARTITION BY region
            ORDER BY revenue_lakh DESC, month_start
        ) AS rn
    FROM monthly_sales
)
SELECT region, month_start, revenue_lakh
FROM ranked
WHERE rn <= 2;

This returns East's April (20) and February (15), and West's March (22) and April (19). DuckDB, Snowflake and BigQuery also offer a QUALIFY clause that lets you filter on the window function directly. It is not available in PostgreSQL, MySQL or SQLite, so the CTE pattern is the portable one.

Which window function should you use for which task?

Match the task to the function using the table below.

Which databases support window functions?

All major databases support them in current versions, but older installations may not. Check your version before you rely on them.

What mistakes do people make with window functions?

1. Filtering with WHERE. As shown above, wrap the query in a CTE or subquery, then filter.
2. Forgetting the tiebreaker. ROW_NUMBER() OVER (ORDER BY revenue_lakh DESC) is not repeatable when two rows tie. Add a second sort column.
3. Misreading LAST_VALUE. When you add ORDER BY and no frame, the default frame ends at the current row, so LAST_VALUE returns the current row's own value. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING if you want the last row of the whole partition.
4. Ignoring NULLs from LAG and LEAD. A subtraction involving NULL gives NULL. Use a default value or COALESCE when you need a number.
5. Dropping PARTITION BY by accident. Without it, the window spans the whole table and your "per region" numbers become "overall" numbers. The query still runs, which makes this error easy to miss.

When should you not use a window function?

You may not need one. A window function is the right tool for row-level detail next to a group-level number, and plain SQL is often enough otherwise.

• You only need a summary. If the output is one row per region, GROUP BY is shorter and easier to read. Do not add OVER and then remove duplicates with DISTINCT.
• Your database is too old. MySQL 5.7 and older do not support window functions. Upgrade, or use a self-join or a variable-based workaround, and note that the workaround is harder to maintain.
• The table is very large and the sort is expensive. Each distinct PARTITION BY and ORDER BY combination can force another sort. Filter rows early, check the query plan, and avoid defining five different windows in one query without a reason.
• Your team cannot read it. A window query that a colleague cannot maintain is a cost. Use a CTE, name your columns clearly and add a comment when the logic is not obvious.
• The calculation belongs elsewhere. A moving average that feeds a chart may be simpler to compute in pandas or in your BI tool, if the data is already there.

What should you do next?

1. Run the sample. Create the monthly_sales table in DuckDB or SQLite and reproduce every result table above. If your numbers differ, find out why before you move on.
2. Rewrite one of your own queries. Pick a report where you used a self-join to get the previous period, and rewrite it with LAG. Compare the two outputs row by row. Our guide to SQL joins will help you recognise the pattern.
3. Practise the interview patterns. Write from memory: top N per group, month-on-month change, running total and a 3-month moving average. Then try the same tasks on a CSV file with DuckDB, as shown in our guide to querying CSV and Parquet files with DuckDB in Python. If your GROUP BY basics are shaky, revise them first in SQL GROUP BY explained.

Ivy has been teaching data science and AI since 2008 and has trained 37,500+ alumni. Learners practise SQL on real business datasets, with a trainer reviewing their queries.

Frequently asked questions

What is the difference between GROUP BY and a window function?

GROUP BY collapses many rows into one row per group. A window function keeps every row and adds a calculated value to each one. Use GROUP BY when you want a summary table, and a window function when you need row-level detail next to a group-level number.

Can I use a window function in a WHERE clause?

No. The database evaluates WHERE before it calculates window functions, so the result does not exist yet. Put the window function in a subquery or a CTE and filter in the outer query. DuckDB, Snowflake and BigQuery also offer a QUALIFY clause for this.

Do window functions work in MySQL and SQLite?

Yes, in recent versions. MySQL supports window functions from version 8.0, and SQLite from version 3.25. MySQL 5.7 and older do not support them. PostgreSQL, SQL Server and DuckDB also support them.

Are window functions slow?

They need the database to sort or partition the rows, which costs time on large tables. They are often faster and clearer than the self-joins they replace. Check the query plan and filter rows early if a query on a large table is slow.

What does LAG return for the first row?

LAG returns NULL for the first row of each partition, because there is no earlier row to read. You can pass a default value as the third argument, such as LAG(revenue_lakh, 1, 0), or wrap the result in COALESCE.

References

1. PostgreSQL documentation, Tutorial: Window Functions. postgresql.org/docs/current/tutorial-window.html
2. SQLite documentation, Window Functions. sqlite.org/windowfunctions.html
3. MySQL 8.0 Reference Manual, Window Functions. dev.mysql.com/doc/refman/8.0/en/window-functions.html
4. DuckDB documentation, Window Functions. duckdb.org/docs/stable/sql/functions/window_functions