SQL · Guides · October 29, 2026 · 11 min read

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

A window function computes a value for each row from a set of related rows (the “window”) without collapsing those rows into one. GROUP BY turns 33,121 flights into six status rows; COUNT(*) OVER (PARTITION BY status) keeps all 33,121 flights and writes the count of each one’s status next to it. That single idea gives you rankings, top N per group, running totals, moving averages, shares of a total and comparisons with the previous row.

The syntax is always the same:

function(...) OVER (
  PARTITION BY ...   -- optional: restart the calculation for each group
  ORDER BY ...       -- optional: order rows inside the window
  ROWS/RANGE ...     -- optional: which rows around the current one to use
)

All examples run on the airline database of the Nomto SQL course (104 airports, 9 aircraft models, 33,121 flights in July–September 2026, 262,788 bookings; money in US dollars), in PostgreSQL syntax. Each output below is real, and you can run any query in the Nomto SQL room to check it.

OVER (): an aggregate next to every row

The simplest window is the whole result. Compare each model’s range with the fleet average:

SELECT model, range,
       ROUND(AVG(range) OVER ()) AS fleet_avg,
       range - ROUND(AVG(range) OVER ()) AS diff
FROM aircrafts
ORDER BY range DESC
LIMIT 5;

Without a window you would need a subquery or a join to a one-row table to get fleet_avg onto each row.

Share of the total

Window functions run after GROUP BY, so you can wrap an aggregate in a window. SUM(COUNT(*)) OVER () is the sum of all group counts:

SELECT status, COUNT(*) AS flights,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM flights
GROUP BY status
ORDER BY flights DESC;

The 100.0 matters: with integers, PostgreSQL would do integer division and most shares would come out as 0.

PARTITION BY: a separate window per group

PARTITION BY splits rows into groups and computes the window inside each one, like GROUP BY but without merging rows. Share of revenue by class within each aircraft model:

SELECT a.model, tf.fare_conditions,
       SUM(tf.amount) AS revenue,
       ROUND(100.0 * SUM(tf.amount) / SUM(SUM(tf.amount)) OVER (PARTITION BY a.model), 1) AS pct_of_model
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
WHERE a.aircraft_code IN ('773', 'SU9')
GROUP BY a.model, tf.fare_conditions
ORDER BY a.model, revenue DESC;

The percentages add up to 100 within each model, not across the table.

ROW_NUMBER vs RANK vs DENSE_RANK

All three number rows in the order given by ORDER BY inside OVER. They differ only on ties:

• ROW_NUMBER() gives every row a unique number; ties are broken arbitrarily unless you add a tie-breaker.
• RANK() gives tied rows the same number and then skips: 1, 2, 2, 4.
• DENSE_RANK() gives tied rows the same number and does not skip: 1, 2, 2, 3.

Rank airports by the number of distinct destinations. The interesting part is around tenth place, where five airports tie with 9 destinations:

WITH d AS (
  SELECT departure_airport, COUNT(DISTINCT arrival_airport) AS destinations
  FROM flights
  GROUP BY departure_airport
), r AS (
  SELECT departure_airport, destinations,
         ROW_NUMBER() OVER (ORDER BY destinations DESC, departure_airport) AS row_num,
         RANK()       OVER (ORDER BY destinations DESC) AS rnk,
         DENSE_RANK() OVER (ORDER BY destinations DESC) AS dense_rnk
  FROM d
)
SELECT * FROM r
WHERE row_num BETWEEN 8 AND 15
ORDER BY row_num;

After the five-way tie, RANK jumps to 15 (four places are “used up”), while DENSE_RANK continues with 11. The departure_airport tie-breaker in ROW_NUMBER makes its result repeatable; without it, IKT, KZN, PEE, SGC and TJM could come out in any order on a different run.

Which to use:

• “Give me exactly N rows” or “remove duplicates”: ROW_NUMBER.
• “Olympic” ranking where ties share a place and the next place is skipped: RANK.
• “Top 3 distinct values”, for example the three highest prices, however many rows have each: DENSE_RANK.

Top N per group

The classic use of ROW_NUMBER with PARTITION BY: the two most frequent destinations from each Moscow airport.

WITH r AS (
  SELECT departure_airport, arrival_airport, COUNT(*) AS flights,
         ROW_NUMBER() OVER (PARTITION BY departure_airport
                            ORDER BY COUNT(*) DESC, arrival_airport) AS rn
  FROM flights
  WHERE departure_airport IN ('SVO', 'DME', 'VKO')
  GROUP BY departure_airport, arrival_airport
)
SELECT departure_airport, arrival_airport, flights, rn
FROM r
WHERE rn <= 2
ORDER BY departure_airport, rn;

Two things to notice:

1. You cannot filter a window function in WHERE. WHERE runs before windows are computed, so WHERE ROW_NUMBER() OVER (...) <= 2 is an error. Compute the number in a CTE or subquery, then filter outside. (Some engines offer a QUALIFY clause for this; PostgreSQL does not.)
2. Check for ties. From Vnukovo, BZK and LED both have 183 flights; ROW_NUMBER put BZK first only because of the alphabetical tie-breaker. If ties should both be shown, use RANK() ... <= 2; that can return more than two rows per group.

Deduplication: the latest row per key

The same pattern keeps one row per key. The last arrived departure of each flight number from Kazan:

WITH ranked AS (
  SELECT flight_no, arrival_airport, scheduled_departure,
         ROW_NUMBER() OVER (PARTITION BY flight_no ORDER BY scheduled_departure DESC) AS rn
  FROM flights
  WHERE departure_airport = 'KZN' AND status = 'Arrived'
)
SELECT flight_no, arrival_airport, scheduled_departure
FROM ranked
WHERE rn = 1
ORDER BY flight_no
LIMIT 5;

Running totals

Add ORDER BY inside OVER to an aggregate and it becomes cumulative. Daily bookings in the first week of August with a running total:

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  WHERE book_date >= '2026-08-01' AND book_date < '2026-08-08'
  GROUP BY CAST(book_date AS date)
)
SELECT day, bookings,
       SUM(bookings) OVER (ORDER BY day) AS running_total,
       LAG(bookings) OVER (ORDER BY day) AS prev_day,
       bookings - LAG(bookings) OVER (ORDER BY day) AS change
FROM daily
ORDER BY day;

Add PARTITION BY to restart the running total per group, for example per airport or per month.

LAG and LEAD: the previous and next row

LAG(x) returns x from the previous row in the window order, LEAD(x) from the next one. The table above uses LAG for day-over-day change. On the first row there is no previous row, so the result is NULL; LAG(bookings, 1, 0) would return 0 instead, and LAG(bookings, 7) would look a week back.

LEAD answers “what happens next”. How often does flight PG0579 fly, and when is its last departure?

SELECT flight_no,
       CAST(scheduled_departure AS date) AS day,
       LEAD(CAST(scheduled_departure AS date)) OVER (ORDER BY scheduled_departure)
         - CAST(scheduled_departure AS date) AS days_to_next
FROM flights
WHERE flight_no = 'PG0579'
ORDER BY scheduled_departure DESC
LIMIT 3;

A weekly flight; the last departure in the schedule has no next one. Note that the window’s ORDER BY (ascending) is independent of the query’s ORDER BY (descending): the window is computed first, then the output is sorted.

When comparing rows of many entities at once, always partition: LEAD(...) OVER (PARTITION BY flight_no ORDER BY scheduled_departure). Without it, the last departure of one flight would be compared with the first departure of the next flight number.

Window frames: ROWS vs RANGE

The frame defines which rows around the current one the function sees. Two things to remember.

The default frame. With ORDER BY and no explicit frame, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats all rows with the same ORDER BY value as one step, so tied rows get the same running total:

WITH d AS (
  SELECT departure_airport, COUNT(DISTINCT arrival_airport) AS destinations
  FROM flights
  GROUP BY departure_airport
), w AS (
  SELECT departure_airport, destinations,
         SUM(destinations) OVER (ORDER BY destinations DESC) AS range_total,
         SUM(destinations) OVER (ORDER BY destinations DESC, departure_airport
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total
  FROM d
)
SELECT * FROM w
WHERE destinations BETWEEN 9 AND 10
ORDER BY destinations DESC, departure_airport;

With RANGE, all five tied airports jump straight to 261. With ROWS, the total grows one row at a time. If your running total “steps” on equal timestamps, this is why. For a row-by-row running total, use ROWS and a unique ordering.

Moving averages. A frame like ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a 3-row moving window:

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  WHERE book_date >= '2026-08-01' AND book_date < '2026-08-08'
  GROUP BY CAST(book_date AS date)
)
SELECT day, bookings,
       ROUND(AVG(bookings) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_3d
FROM daily
ORDER BY day;

The first two rows average over fewer than three days, because there is nothing before 1 August in this window. ROWS counts rows, not days: if a day were missing from daily, the “3-day” average would silently span four calendar days. Fill gaps with a date series first, or use RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW on a date column.

Where window functions fit in query order

Logically, windows are computed after FROM, WHERE, GROUP BY and HAVING, and before ORDER BY and LIMIT. That explains three rules:

• A window sees only the rows that survived WHERE; to rank all flights but show some, rank in a subquery and filter outside.
• You can use aggregates inside a window (SUM(COUNT(*)) OVER ()) because grouping has already happened.
• You cannot use a window function in WHERE, GROUP BY or HAVING; wrap the query instead.

For how WHERE, GROUP BY and HAVING interact, see GROUP BY vs HAVING. Ranking and “top N per group” are also among the most common SQL interview questions.

FAQ

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

GROUP BY returns one row per group. A window function returns every input row and adds a value computed over a group of rows. You can use both in one query: the window is applied to the grouped result.

What is the difference between RANK and DENSE_RANK?

Both give tied rows the same number. After a tie, RANK skips numbers (1, 2, 2, 4), DENSE_RANK does not (1, 2, 2, 3). In the airport example, five airports tie at 10th place; the next one is 15th by RANK and 11th by DENSE_RANK.

Why is my running total the same on several rows?

Because the default frame is RANGE, which treats rows with equal ORDER BY values as peers and includes all of them at once. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and make the ordering unique.

Can I use a window function in WHERE?

No. Compute it in a CTE or subquery and filter in the outer query.

Does ROW_NUMBER need ORDER BY?

PostgreSQL allows ROW_NUMBER() OVER () but the numbering is then arbitrary. For any result you want to reproduce, give it an ORDER BY with a unique tie-breaker.

Also in Português (Brasil), Tiếng Việt, Español, 简体中文