What is a window function?
A window function looks at a group of rows related to the current row and returns one value for that row. You have used aggregates like SUM and COUNT with GROUP BY, which squash a group into one row. A window function does the same kind of calculation but keeps every row, and adds the answer as a new column beside it.
You recognise one by the word OVER straight after the function. SUM(birds) OVER () adds up every sighting and puts that total on every row. The brackets after OVER describe the window: which rows count as related to this one, and in which order they are read.
SQLite has supported window functions since version 3.25 (2018), and PostgreSQL and MySQL 8 support the same syntax. Older MySQL 5.7 has none, which is a problem if an old hosting plan runs your database.
SELECT species, birds,
SUM(birds) OVER () AS club_total,
ROUND(100.0 * birds / SUM(birds) OVER (), 1) AS pct_of_club
FROM sightings;How is this different from GROUP BY?
GROUP BY member_id returns one row per member, so you can see each member's total but not the sightings behind it. The window version below returns all 16 sightings, and each carries its member's total. You can compare one sighting with its member's total on the same row, which GROUP BY can't do without a join back to the original table.
Use GROUP BY when the answer should have one row per group: a leaderboard, a monthly summary. Use a window function when the answer is still one row per original row, with extra context: a rank, a share of the total, a running sum, the previous value.
Open the Rows view on the example. Window functions are worked out late, at the SELECT step, after WHERE and GROUP BY have already chosen the rows. That order explains a rule you will meet soon: you can't filter on a window function in WHERE.
SELECT m.name, s.species, s.birds,
SUM(s.birds) OVER (PARTITION BY s.member_id) AS member_total
FROM sightings AS s
JOIN members AS m ON m.id = s.member_id
ORDER BY m.name, s.birds DESC;What does PARTITION BY do?
PARTITION BY splits the rows into separate windows, one per value, and the function runs inside each window on its own. PARTITION BY member_id gives Priya one window, Owen another, and so on. It is the window version of GROUP BY, except the rows stay separate.
Leave PARTITION BY out and the whole result is one window. That is fine for a club-wide total, and wrong when each member should have their own rank or running total. If every member's numbers look suspiciously like they continue from someone else's, a missing PARTITION BY is the usual cause.
How do I number and rank rows?
Three functions give positions, and they differ only in how they treat ties. They all need an ORDER BY inside OVER, because a position only means something once you say what you are sorting by.
ROW_NUMBER() gives 1, 2, 3, 4 with no repeats, even when two rows are equal; which of the tied rows comes first is not defined. RANK() gives tied rows the same number and then skips, like a race result: 1, 2, 2, 4. DENSE_RANK() gives ties the same number without skipping: 1, 2, 2, 3. Owen's 9 redshank and 9 oystercatchers show all three behaviours in the query below.
Pick by the question. 'Number these rows so I can page through them' wants ROW_NUMBER. 'Who came second?' wants RANK or DENSE_RANK, depending on whether the place after a tie should be third or fourth. Add a second sort key, such as seen_on, if you need ROW_NUMBER to be repeatable.
SELECT member_id, species, birds,
ROW_NUMBER() OVER w AS row_num,
RANK() OVER w AS rank_with_gaps,
DENSE_RANK() OVER w AS rank_no_gaps
FROM sightings
WINDOW w AS (PARTITION BY member_id ORDER BY birds DESC)
ORDER BY member_id, birds DESC;How does a running total work?
Put ORDER BY inside OVER on an aggregate like SUM, and the window for each row becomes 'from the start of the partition up to this row'. Each row's total includes itself and everything before it. That is the task: SUM(birds) OVER (PARTITION BY member_id ORDER BY seen_on).
Strictly, the default window runs up to the last row that has the same ORDER BY value as the current one. If two sightings shared a date, both would show the same running total, already including each other. Here every member has one sighting per date, so this never shows. When dates can repeat, sort by seen_on, id, or write the window out as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
The ORDER BY inside OVER and the ORDER BY at the end of the query are separate. The inner one decides how the total accumulates. The outer one only decides how rows are displayed. Keep both, and keep them in step, so a reader sees the totals growing down the page.
How do I compare a row with the one before it?
LAG(column) returns the value of that column from the previous row in the window, and LEAD(column) from the next row. Subtract and you get the change since last time: did this member count more or fewer birds than at their previous outing?
The first row in each partition has no previous row, so LAG gives NULL, and anything subtracted from NULL is NULL too. That is usually honest: there is nothing to compare with. If you need a number, LAG takes a default as its third argument, LAG(birds, 1, 0), or wrap it in COALESCE. The second argument is how many rows back to look.
SELECT member_id, seen_on, species, birds,
LAG(birds) OVER w AS previous,
birds - LAG(birds) OVER w AS change,
LEAD(seen_on) OVER w AS next_outing
FROM sightings
WINDOW w AS (PARTITION BY member_id ORDER BY seen_on)
ORDER BY member_id, seen_on;How do I keep only the top row per group?
A common request is 'each member's biggest sighting'. You can't write WHERE RANK() OVER (…) = 1, because WHERE runs before window functions exist. SQLite answers with 'misuse of window function'.
Instead, rank in one step and filter in the next. A WITH step from the previous lessons fits well: the first step adds the rank, and the outer query keeps rows where the rank is 1. Because RANK shares places, a member with a tie at the top returns two rows. Use ROW_NUMBER when you need exactly one row per member, and decide on a tie-breaker.
WITH ranked AS (
SELECT member_id, species, birds,
RANK() OVER (PARTITION BY member_id ORDER BY birds DESC) AS place
FROM sightings
)
SELECT m.name, r.species, r.birds
FROM ranked AS r
JOIN members AS m ON m.id = r.member_id
WHERE r.place = 1;What do AI tools get wrong with window functions?
To check one, pick a single person, add up their rows by hand for the first three lines, and compare with the column.
- A missing PARTITION BY, so a ranking or running total that should restart for each person runs across the whole table.
- ROW_NUMBER where the question involves ties. It silently gives tied rows different places.
- A window function inside WHERE or HAVING. It fails in every database. The fix is a WITH step.
- A running total with no ORDER BY inside OVER, which shows the full total on every row instead of a growing one.
- Using window syntax on MySQL 5.7, which has none. Ask which database version will run the query.
Field notes
- A window function returns one value for each row, using a group of related rows.
GROUP BYcollapses rows into one per group, while a window function keeps them all.PARTITION BYgives each value, such as each member, its own separate window.ORDER BYinsideOVERonSUMmakes a running total up to the current row.
Easy to get wrong
WHERE runs before SELECT, so the window column doesn't exist yet. Compute it in a WITH step, then filter.
Without ORDER BY inside OVER, the window is the whole partition, so every row shows the member's full total.
ROW_NUMBER never repeats a number, so two equal counts get different places in no reliable order.
Questions people ask
Can I use GROUP BY and a window function in the same query?
Yes. GROUP BY runs first, then the window function works on the grouped rows. RANK() OVER (ORDER BY SUM(birds) DESC) ranks members by their totals in one query.
What does the WINDOW clause do?
It names a window so several functions can share it: WINDOW w AS (PARTITION BY member_id ORDER BY seen_on), then OVER w. It saves repeating the same brackets.
Are window functions slow?
They sort the rows once per distinct window. On thousands of rows that is quick. On millions, an index matching the PARTITION BY and ORDER BY columns helps.
What other window functions exist?
FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE (split rows into equal buckets), PERCENT_RANK and CUME_DIST. Any aggregate, including AVG, MIN and MAX, also works with OVER.