SQL window functions explained: ROW_NUMBER, RANK, LAG, running totals

SQL window functions calculate a value for each row from a set of related rows without collapsing those rows into one, the way GROUP BY does. The OVER clause defines the set: PARTITION BY splits rows into groups, ORDER BY sorts them, and an optional frame picks the rows around the current one. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and running totals all work this way.

What is a window function in SQL?

A window function is a function that calculates across rows related to the current row, written with an OVER clause directly after the function name and arguments. PostgreSQL's tutorial says the rows keep their separate identities, unlike a non-window aggregate call. The MySQL 8.4 manual puts the contrast the same way: an aggregate groups query rows into a single result row, whereas a window function produces a result for each query row.

Any ordinary aggregate such as SUM or AVG becomes a window function when OVER follows it, and otherwise returns one row for the whole set. The examples below use one hypothetical sales table, with units sold by seven reps in two regions.

Two reps in North, Bilal and Chitra, tie on 45 units. That tie is what separates the ranking functions below.

How do PARTITION BY and ORDER BY work inside a window?

PARTITION BY divides the rows into groups that share the same values, and the function is computed within each group. ORDER BY inside OVER sets the order in which rows are processed within each group. The table below lists the three parts of an OVER clause and what happens when each is left out.

The default frame depends on ORDER BY. PostgreSQL's manual says that with ORDER BY the frame runs from the start of the partition through the current row and any rows tied with it, and without ORDER BY it covers the whole partition. MySQL 8.4 documents the same defaults.

An ORDER BY inside OVER does not sort the final output. The MySQL 8.4 manual says it applies within partitions and that sorting the whole result needs an ORDER BY at the top level of the query.

What is the difference between GROUP BY and PARTITION BY?

GROUP BY returns one row per group, while PARTITION BY keeps every input row and attaches the group's result to each one. On the sales table, GROUP BY region returns two rows.

SELECT region, SUM(units) AS region_total
FROM sales
GROUP BY region;

The window version returns all seven rows, each carrying its region total. That makes it possible to show a rep's units next to the regional total in one query, for example to work out a share of the total.

SELECT region, rep, units,
       SUM(units) OVER (PARTITION BY region) AS region_total
FROM sales;

How do ROW_NUMBER, RANK and DENSE_RANK differ when rows tie?

ROW_NUMBER gives every row a unique position, RANK gives tied rows the same number and then skips ahead, and DENSE_RANK gives tied rows the same number without skipping. PostgreSQL's manual describes RANK as the row number of the first row in its peer group and DENSE_RANK as effectively counting peer groups.

SELECT region, rep, units,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY units DESC, rep) AS row_num,
  RANK()       OVER (PARTITION BY region ORDER BY units DESC)      AS rnk,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY units DESC)      AS dense_rnk
FROM sales;

Bilal and Chitra both get 2 from RANK and DENSE_RANK. Dev then gets 4 from RANK, because two rows share second place, and 3 from DENSE_RANK. ROW_NUMBER separates the tie only because the query adds rep as a tie-breaker: PostgreSQL's tutorial says tied rows are numbered in an unspecified order, and MySQL's manual calls ROW_NUMBER without ORDER BY nondeterministic.

How do LAG and LEAD compare a row with its neighbours?

LAG returns a value from an earlier row in the same partition and LEAD returns one from a later row, which makes month-on-month comparisons possible without a self join. Both take an offset that defaults to 1 and a default value that defaults to NULL when no such row exists. The MySQL 8.4 manual gives the same signature.

The next example uses a hypothetical monthly revenue table, with revenue in Rs lakh.

SELECT month, revenue,
       LAG(revenue)  OVER (ORDER BY month) AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly_revenue;

The first month has no previous row, so LAG returns NULL and the change is NULL. Writing LAG(revenue, 1, 0) returns 0 there instead.

How do you calculate a running total and a moving average?

A running total is an aggregate used as a window function with ORDER BY. PostgreSQL's manual says an aggregate with ORDER BY and the default frame produces a running sum. A moving average needs a frame clause that limits how many earlier rows are included.

SELECT month, revenue,
  SUM(revenue) OVER (ORDER BY month) AS running_total,
  ROUND(AVG(revenue) OVER (ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_last_3
FROM monthly_revenue;

The first two averages use fewer than three months because the frame stops at the start of the partition. A moving average smooths a metric to show whether it moved, while A/B testing is the method for testing whether a specific change caused the move.

The default frame has a trap. With ORDER BY, it includes every row tied with the current row, so duplicates in the ordering column make the total jump by the whole tied group at once. In the hypothetical orders below, two orders fall on 2 November.

SELECT order_id, day, amount,
  SUM(amount) OVER (ORDER BY day) AS default_frame,
  SUM(amount) OVER (ORDER BY day, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM daily_orders;

Order 2 shows 90 under the default frame because order 3 is its peer. The ROWS version adds one row at a time, and the unique order_id in its ORDER BY makes the order of the two November rows fixed. For a row-by-row running total, write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

What are three window function patterns worth practising?

Three patterns are worth practising: top N per group, the second highest value with ties, and month-on-month change. Month-on-month change is the LAG query above. The other two use a ranking function inside a subquery. More practice questions of this kind are in an earlier post on data analyst interview questions for freshers.

Top 2 reps per region uses ROW_NUMBER. PostgreSQL's tutorial shows the same shape, filtering a subquery on a row_number below 3 to keep the first two rows per department.

SELECT region, rep, units
FROM (SELECT region, rep, units,
             ROW_NUMBER() OVER (PARTITION BY region
                                ORDER BY units DESC, rep) AS rn
      FROM sales) t
WHERE rn <= 2;

The second highest value per region, keeping ties, uses DENSE_RANK. Filtering ROW_NUMBER = 2 would return only Bilal for North and silently drop Chitra, who has the same 45 units.

SELECT region, rep, units
FROM (SELECT region, rep, units,
             DENSE_RANK() OVER (PARTITION BY region
                                ORDER BY units DESC) AS d
      FROM sales) t
WHERE d = 2;

Where do window functions go wrong?

Window functions fail in a few predictable ways, and most trace back to evaluation order or to the default frame. Duplicate or missing values in the source data also distort rankings, which is why data cleaning techniques come before analysis.

• They cannot appear in WHERE, GROUP BY or HAVING. PostgreSQL's manual says they are allowed only in the SELECT list and ORDER BY because they run after WHERE, GROUP BY and HAVING, so filtering on a rank needs a subquery, as above. SQLite rejects a window function in WHERE with the message "misuse of window function ROW_NUMBER()".
• LAST_VALUE with the default frame returns the current row's own value. On the monthly revenue table it gave 120, 150, 135, 180, 170 and 210 until the frame was widened with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, which returned 210 on every row. PostgreSQL's manual warns that the default gives unhelpful results for last_value.
• Ranking and offset functions ignore a frame clause in MySQL. The 8.4 manual says MySQL permits a frame clause on ROW_NUMBER, RANK, DENSE_RANK, LAG and LEAD but ignores it.
• PostgreSQL 18, the current release, does not implement the standard's IGNORE NULLS option for LAG and LEAD, so they cannot skip NULL values. The development manual for the next version adds it.

These samples ran on SQLite 3.54.0 and were checked against the PostgreSQL manual. SQLite's documentation says window function support arrived in release 3.25.0 on 15 September 2018 and that the developers tested it against PostgreSQL so the functions behave the same way. The tables are tiny, so nothing here says how a window function performs on millions of rows.

How do SQL window functions fit into a data analytics programme?

Window functions are the step between basic joins and the cohort, funnel and retention queries analysts write for product data. The Analytics in the Age of AI programme (certificate title "Analytics with Agentic AI") is a live online programme by Vishlesan i-Hub Foundation, IIT Patna, with Masai as its ed-tech and implementation partner and an optional campus immersion at IIT Patna. It covers SQL, Python, BI tools, applied machine learning, generative AI workflows and agentic analytics through hands-on projects and a capstone, and its listed SQL topics run from foundations to window functions.

Frequently asked questions

What is a window function in SQL?

A window function is a function written with an OVER clause that calculates a value for each row from a set of related rows, without merging those rows into one. ROW_NUMBER, RANK, LAG and LEAD are built-in window functions, and aggregates such as SUM become window functions when OVER follows them.

What is the difference between RANK and DENSE_RANK?

Both give tied rows the same number. RANK then skips numbers to leave gaps, so two rows sharing second place are followed by fourth. DENSE_RANK continues with the next consecutive number, so the same two rows are followed by third. In the sample data, Dev gets 4 from RANK and 3 from DENSE_RANK.

What is the difference between GROUP BY and PARTITION BY?

GROUP BY collapses each group into a single output row. PARTITION BY, used inside OVER, keeps every input row and attaches the group's calculation to each one. On the sample data, GROUP BY region returns 2 rows, while SUM(units) OVER (PARTITION BY region) returns all 7 rows.

Can a window function be used in a WHERE clause?

No. PostgreSQL's manual says window functions are allowed only in the SELECT list and ORDER BY, because they run after WHERE, GROUP BY and HAVING. To filter on a rank, compute it in a subquery and put the condition in the outer query's WHERE clause.

What does LAG return for the first row?

LAG returns NULL for the first row of a partition, because no earlier row exists. PostgreSQL's manual says the default value is NULL when omitted. A third argument replaces it, so LAG(revenue, 1, 0) returns 0 for the first month in the sample revenue table.

Does MySQL support window functions?

Yes. The MySQL 8.4 manual documents window functions including ROW_NUMBER, RANK, DENSE_RANK, LAG and LEAD, with the same OVER, PARTITION BY and ORDER BY syntax as PostgreSQL. One difference is that MySQL accepts but ignores a frame clause on ranking and offset functions.

Next step

Run each query above on a small table of your own and change the tied values to see how the ranks shift. The Analytics in the Age of AI programme covers SQL through window functions alongside Python and BI tools.