SQL Window Functions Explained: OVER, PARTITION BY, RANK

GROUP BY answers "what is the total per shop?" by folding each shop's rows into one. Very often you want the total and the rows: a running balance next to each transaction, each employee's rank within their team, today's sales next to yesterday's. Window functions do exactly that. They compute across a set of related rows and attach the answer to every row, without removing any. Every query below was run on SQLite 3.37, and the output shown is real.

The problem it solves

Before window functions, a running total needed a self-join or a correlated subquery for every row, both awkward to write and often slow. "Top three per category" needed a nested query that counted rows ahead of each one. With a window function, each of these is one extra column in the SELECT.

The intuition

A window function is an ordinary function followed by OVER (...), and the parentheses describe the window: which rows the function may look at for the current row.

• PARTITION BY shop splits the rows into groups. The calculation restarts in each group, like GROUP BY — but the rows stay.
• ORDER BY day orders the rows within each partition. For running totals and rankings, "so far" means "up to this row in this order".
• A frame clause such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW narrows the window further, to a sliding range of rows around the current one.

There is one more thing to know: window functions are computed after WHERE, GROUP BY and HAVING. That is why you cannot filter on them directly — the WHERE clause runs before they exist.

Watch it run

The animation uses the lesson's four days of baking. GROUP BY produces the total but folds the four rows into one. OVER (ORDER BY day) does the same addition row by row: the first row sees only itself, the second sees two rows, and the last row's running total equals the GROUP BY answer. A second window then ranks the same rows by loaves, in the same query.

The code

Two bakeries, a few days each. Note that the north shop sold 55 loaves on two different days — ties matter later.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE sale (shop TEXT, day INTEGER, loaves INTEGER);
  INSERT INTO sale VALUES
    ('north', 1, 40), ('north', 2, 55), ('north', 3, 35), ('north', 4, 55),
    ('south', 1, 20), ('south', 2, 30), ('south', 3, 25);
""")

def show(sql):
    for row in db.execute(sql):
        print(row)

show("""SELECT shop, day, loaves,
               SUM(loaves) OVER (PARTITION BY shop ORDER BY day) AS so_far
        FROM sale ORDER BY shop, day""")
# ('north', 1, 40, 40)
# ('north', 2, 55, 95)
# ('north', 3, 35, 130)
# ('north', 4, 55, 185)
# ('south', 1, 20, 20)
# ('south', 2, 30, 50)
# ('south', 3, 25, 75)

The running total restarts at the south shop because of PARTITION BY. Every input row is still there.

Ranking. Three functions number rows, and they differ only on ties. ROW_NUMBER never repeats. RANK gives ties the same number and then skips. DENSE_RANK gives ties the same number and does not skip:

show("""SELECT day, loaves,
               ROW_NUMBER() OVER w AS row_num,
               RANK()       OVER w AS rnk,
               DENSE_RANK() OVER w AS dense
        FROM sale WHERE shop = 'north'
        WINDOW w AS (ORDER BY loaves DESC)
        ORDER BY loaves DESC, day""")
# (2, 55, 1, 1, 1)
# (4, 55, 2, 1, 1)
# (1, 40, 3, 3, 2)
# (3, 35, 4, 4, 3)

Which of the two 55s gets ROW_NUMBER 1 is not defined by SQL; add a tiebreaker such as day to the window's ORDER BY if it matters. The WINDOW w AS (...) clause just names a window so several functions can share it.

Looking at neighbours and sliding frames. LAG reads a value from the previous row, and a ROWS frame gives a moving average over the current row and the two before it:

show("""SELECT day, loaves,
               loaves - LAG(loaves) OVER (ORDER BY day) AS change,
               ROUND(AVG(loaves) OVER (ORDER BY day
                     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg3
        FROM sale WHERE shop = 'north' ORDER BY day""")
# (1, 40, None, 40.0)
# (2, 55, 15, 47.5)
# (3, 35, -20, 43.3)
# (4, 55, 20, 48.3)

Day 1 has no previous row, so LAG returns NULL, and the first averages cover fewer than three rows.

The default frame surprise. With ORDER BY and no frame clause, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE works on values, not rows, so every row tied with the current one is included:

show("""SELECT loaves,
               SUM(loaves) OVER (ORDER BY loaves) AS default_frame,
               SUM(loaves) OVER (ORDER BY loaves
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
        FROM sale WHERE shop = 'north' ORDER BY loaves""")
# (35, 35, 35)
# (40, 75, 75)
# (55, 185, 130)
# (55, 185, 185)

Both 55 rows show 185 with the default frame: the running total jumps over the tie. The ROWS frame adds one row at a time. When the ordering column can repeat, choose the frame on purpose.

Top one per group. A window function cannot appear in WHERE, so compute it in a subquery and filter outside:

try:
    db.execute("""SELECT shop, day FROM sale
                  WHERE ROW_NUMBER() OVER (PARTITION BY shop ORDER BY loaves DESC) = 1""")
except sqlite3.OperationalError as e:
    print(e)
# misuse of window function ROW_NUMBER()

show("""SELECT shop, day, loaves FROM (
          SELECT *, ROW_NUMBER() OVER (PARTITION BY shop ORDER BY loaves DESC, day) AS rn
          FROM sale)
        WHERE rn = 1 ORDER BY shop""")
# ('north', 2, 55)
# ('south', 2, 30)

Finally, SQLite is checked against the definitions written in plain Python — a running total over the partition, a rank as one plus the number of rows strictly ahead, and the RANGE frame as every row whose value is at most the current one — on 300 random tables with many ties:

import random

random.seed(9)
ok = True
for _ in range(300):
    rows = [(random.choice("ab"), d, random.randint(0, 5)) for d in range(random.randint(0, 12))]
    t = sqlite3.connect(":memory:")
    t.execute("CREATE TABLE s (shop TEXT, day INTEGER, n INTEGER)")
    t.executemany("INSERT INTO s VALUES (?, ?, ?)", rows)
    got = t.execute("""SELECT shop, day,
                              SUM(n) OVER (PARTITION BY shop ORDER BY day),
                              RANK() OVER (PARTITION BY shop ORDER BY n DESC),
                              SUM(n) OVER (PARTITION BY shop ORDER BY n)
                       FROM s ORDER BY shop, day""").fetchall()
    want = []
    for shop, day, n in sorted(rows):
        mine = [r for r in rows if r[0] == shop]            # the partition
        running = sum(r[2] for r in mine if r[1] <= day)     # rows up to this day
        rank = 1 + sum(1 for r in mine if r[2] > n)          # 1 + rows strictly ahead
        peers = sum(r[2] for r in mine if r[2] <= n)         # RANGE frame: ties included
        want.append((shop, day, running, rank, peers))
    ok &= got == want
print(ok)                                                    # True

The complexity

A database typically evaluates a window by sorting the rows on the partition and order keys, O(n log n), then making one pass over the sorted rows. Running sums, ranks and LAG are then O(1) per row. An index that already provides that order can remove the sort. Sliding frames are cheap for sums, which can add the new row and subtract the old one; other aggregates may cost more per row, depending on the engine.

Where it goes wrong

• Filtering in WHERE. Window results do not exist yet at that point. Wrap the query and filter outside.
• Relying on the default frame with ties. RANGE includes all peers. Write ROWS BETWEEN ... for a strict row-by-row total.
• Choosing the wrong ranking function. "Top 3 per group" with RANK can return more than three rows when there are ties; with ROW_NUMBER it returns exactly three but picks among ties arbitrarily.
• Forgetting ORDER BY inside OVER. Without it, SUM(...) OVER (PARTITION BY shop) is the whole partition's total on every row, not a running total.

How to say it in an interview

"A window function computes over a set of rows related to the current row and returns a value per row, so, unlike GROUP BY, no rows are collapsed. PARTITION BY restarts the calculation per group, ORDER BY defines 'so far', and a frame clause can narrow it to a sliding range. ROW_NUMBER, RANK and DENSE_RANK differ only on ties. Windows are evaluated after WHERE, so to filter on one — top-N per group — I use a subquery or CTE."

Why WHERE cannot see a window is clearest in SQL's execution order, and the folding behaviour it avoids is in aggregations and GROUP BY.