Intro to Git — Snapshots, Branches & Sharing
WorkshopyOfficial10 stepsQuiz9 interactiveDiagram
gitversion controlbeginners
SQL window functions exercises: running totals, RANK and ties, moving averages with ROWS BETWEEN, LAG, and top N per group, with the window drawn as it slides.

WorkshopyOfficial
78 lessons · used by 2 teachers
Sign in to copy it to your workspace. It’s free.
11 steps · 2 quizzes · open any step, or read it here
In this lesson you practise SQL window functions on two small tables from work: a running total, a rank inside each group, a moving average and the change from the day before. Every exercise runs here, the window is drawn over the rows as it slides, and you check your answers as you go.
A window function computes a value for each row from a set of rows around it, its window, and keeps every row. Pick a window below and press Play: the amber bar is the window, the blue column fills one row at a time.
Interactive · Watch the window slide over the rows — try it in the lesson
OVER (), and split it by group with PARTITION BYROW_NUMBER, RANK and DENSE_RANK, and know what each does with tiesROWS BETWEEN, and avoid the default-frame trapLAG and LEAD, and keep the top rows per group with a CTEAbout 85 minutes, with a short check after the first three ideas, a playground, a practice set and a final quiz.
The two tables:
| table | columns | rows |
|---|---|---|
sales |
sale_day, rep, region, amount |
16: four reps (Ana and Ben in North, Chloe and Dario in South), 2 to 5 March 2026 |
tickets |
day, opened |
10: support tickets opened each day, 2 to 11 March 2026 |
The numbers are illustrative.
You know aggregates: SELECT SUM(amount) FROM sales returns one row, 6850. The sixteen sales are gone; only their total is left.
Add OVER () after the aggregate and it becomes a window function:
SELECT sale_day, rep, amount,
SUM(amount) OVER () AS total
FROM sales;
Now all sixteen rows come back, and each one carries the total, 6850. OVER says "compute this over a window of rows"; the empty brackets say the window is the whole result. The rows are not folded together, so you can put each sale beside the total in one query, which is what a share of total needs:
amount * 100.0 / SUM(amount) OVER () AS pct
Every aggregate works this way: SUM, AVG, COUNT, MIN, MAX.
One trap from arithmetic, not from windows: amount * 100 / SUM(amount) OVER () divides a whole number by a whole number, and PostgreSQL, SQL Server and SQLite drop the decimals: 5.84 becomes 5. Write 100.0.
Try it: press Play, then change the function and what the column shows. Pick "amount * 100 / it" to see the whole-number trap. Then do the tasks.
Interactive · OVER (): one window holding every row — try it in the lesson
Takeaway: an aggregate with OVER () is computed over all the rows and written on every row; nothing is grouped away.
PARTITION BY splits the window into groups. SUM(amount) OVER (PARTITION BY region) computes North's total over North's rows and South's over South's, and writes each region's total on every row of that region:
SELECT sale_day, rep, region, amount,
SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;
It looks like GROUP BY, and the difference is the whole point:
| rows out | what each row holds | |
|---|---|---|
GROUP BY region |
2, one per region | the region and its totals; the single sales are gone |
OVER (PARTITION BY region) |
16, all of them | each sale, plus its region's total |
So a partition answers questions that need the row and its group side by side: this sale against the rep's best, this day against the region's average.
Try it: switch Way between Window and GROUP BY and watch the rows fold or stay. Change the function and the partition, press Play, and see the window cover one colour at a time.
Interactive · PARTITION BY keeps the rows, GROUP BY folds them — try it in the lesson
Takeaway: PARTITION BY makes one window per group and keeps every row; GROUP BY makes one row per group.
Some window functions only make sense with an order. ORDER BY inside OVER sorts the rows of each window, and the ranking functions number them in that order:
ROW_NUMBER(): 1, 2, 3, 4 … every row a different number, ties or not.RANK(): tied rows share a number, and the next number skips: 1, 2, 2, 4.DENSE_RANK(): tied rows share a number, and the next one doesn't skip: 1, 2, 2, 3.The sales have ties: Chloe and Dario both sold 500, and three sales are 300. Ranked by amount, highest first, the two 500s are both 5th with RANK, and the next sale, 450, is 7th, because six sales are bigger. With DENSE_RANK that 450 is 6th: it counts the different amounts above it.
Which of two tied rows ROW_NUMBER calls 5 and which 6 is up to the database, and it can change from run to run. When it matters, add a second column to break the tie: ORDER BY amount DESC, sale_day.
ORDER BY inside OVER orders the window, not the result. The rows come back in any order unless the query ends with its own ORDER BY.
Try it: press Play with ROW_NUMBER and watch the tied amounts, then switch to RANK and DENSE_RANK and play again. Then do the tasks.
Interactive · ROW_NUMBER, RANK and DENSE_RANK on tied amounts — try it in the lesson
Takeaway: ROW_NUMBER never ties; RANK shares a place and skips; DENSE_RANK shares a place and doesn't skip.
Four questions on what you've seen so far.
Question 1. `sales` has 16 rows in 2 regions. How many rows does `SELECT region, SUM(amount) OVER (PARTITION BY region) FROM sales` return?
Question 2. Amounts 700, 650, 500, 500, 450, ranked highest first. What is the 450's RANK?
Question 3. And its DENSE_RANK?
Question 4. What does `AVG(amount) OVER ()` put on each row?
Put ORDER BY inside the OVER of an aggregate and it stops being one number for the window: it becomes a running value.
SELECT day, opened,
SUM(opened) OVER (ORDER BY day) AS running
FROM tickets;
For each day the window starts at the first day and ends at this day. On 2 March it holds one row, 42; on 3 March two rows, 42 + 38 = 80; on 11 March all ten, 433. That set of rows is the frame, and here it grows by one row each day.
Add PARTITION BY and each group runs its own total, starting again at its first row: SUM(amount) OVER (PARTITION BY rep ORDER BY sale_day) is each rep's total so far.
Any aggregate runs this way: MAX(opened) OVER (ORDER BY day) is the busiest day so far, COUNT(*) OVER (ORDER BY day) the number of days so far.
Try it: press Play and watch the frame grow. Switch to "Each rep's sales" and see it start again at each colour. Then do the tasks: each one is a function and a window to choose.
Interactive · A running total grows one row at a time — try it in the lesson
Takeaway: ORDER BY inside OVER turns an aggregate into a running one: from the window's first row to the current row.
A running total keeps every day. A moving average keeps only the last few, so it follows the trend without being thrown by one odd day. You choose the frame yourself:
SELECT day, opened,
AVG(opened) OVER (ORDER BY day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3
FROM tickets;
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means: the two rows before this one, and this one. Three rows, and as the current row moves down, the frame slides with it. On 4 March it averages 42, 38 and 51: 43.67. On 5 March the 42 drops out and the 47 comes in: 45.33.
The pieces of a frame:
| bound | means |
|---|---|
UNBOUNDED PRECEDING |
the first row of the window |
2 PRECEDING |
two rows before this one |
CURRENT ROW |
this row |
1 FOLLOWING |
one row after this one |
UNBOUNDED FOLLOWING |
the last row of the window |
Two things to know. The first rows have fewer rows before them, so 2 March averages one row and 3 March two; a report often leaves those out. And ROWS counts rows, not days: if a day were missing from the table, the frame would reach one day further back.
Try it: press Play, then change the rows before and after and watch the frame change size. Look at the weekend, 7 and 8 March. Then do the tasks.
Interactive · A moving average: the frame slides down the days — try it in the lesson
Takeaway: ROWS BETWEEN n PRECEDING AND CURRENT ROW is a frame of a fixed number of rows that slides down with the current row.
In the running total two steps ago you wrote no frame. When OVER has an ORDER BY and no frame, the database uses this one:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
RANGE doesn't count rows: it takes every row whose ORDER BY value is the same as the current row's, its peers. With one row per day in tickets, that changes nothing. In sales there are four rows each day:
SELECT sale_day, rep, amount,
SUM(amount) OVER (ORDER BY sale_day) AS running
FROM sales;
The first row, Ana's 400 on 2 March, doesn't show 400. It shows 1550: the frame ends at the last row of 2 March, so all four of that day's sales are in it. All four rows of the day show 1550, then all four of 3 March show 3200. That is a running total by day, which is sometimes what you want, but it is not a total that grows on every row.
Two fixes, when you want one:
ROWS: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW stops at this row, peers or not.ORDER BY sale_day, rep. With no ties there are no peers.Use both, so the order of the tied rows is fixed too. The same default trips LAST_VALUE: its frame ends at the current row (and its peers), so LAST_VALUE(amount) OVER (PARTITION BY rep ORDER BY sale_day) returns each row's own amount. To read the last row of the partition, give it the whole partition: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
Try it: press Play with the default frame and watch the window take the whole day. Switch the frame to ROWS and play again. Then try LAST_VALUE, partitioned by rep, with each frame, and do the tasks.
Interactive · The default frame takes every tied row — try it in the lesson
Takeaway: with ORDER BY and no frame, the frame is RANGE up to the current row and its ties; write ROWS and a unique order for a row-by-row running total.
How many more tickets than yesterday? That needs two rows on one line: today's and yesterday's. LAG reads a value from an earlier row of the window, LEAD from a later one:
SELECT day, opened,
LAG(opened) OVER (ORDER BY day) AS prev_day,
opened - LAG(opened) OVER (ORDER BY day) AS day_change
FROM tickets;
LAG(opened) is the previous row's opened, in the order of the window's ORDER BY. LAG(opened, 7) is seven rows back: a week ago, in a table with one row per day.opened - NULL is NULL too. A third argument gives a value instead: LAG(opened, 1, 0).LEAD is the same, looking forward: the last row's LEAD is NULL.PARTITION BY rep, Ben's first day reads nothing, not Ana's last day.Like ROWS, LAG counts rows, not days: it reads the previous row, whatever date it has.
Try it: press Play, switch to LEAD, change how many rows away, and show the change instead of the value. Switch to "Every rep's sales" and see what LAG reads without a partition. Then do the tasks.
Interactive · LAG and LEAD: reading another row — try it in the lesson
Takeaway: LAG and LEAD put another row's value on this row; the first (or last) row gets NULL.
Each rep's best day. A ROW_NUMBER per rep, ordered by amount, gives the best sale number 1, so the obvious query is:
SELECT rep, sale_day, amount,
ROW_NUMBER() OVER (PARTITION BY rep ORDER BY amount DESC) AS rn
FROM sales
WHERE rn = 1;
It fails: column "rn" does not exist. Putting the window itself in WHERE fails too: window functions are not allowed in WHERE. The reason is the order a query runs in:
WHERE runs before any window exists. So compute the window first, in a CTE, and filter the CTE:
WITH ranked AS (
SELECT rep, sale_day, amount,
ROW_NUMBER() OVER (PARTITION BY rep ORDER BY amount DESC) AS rn
FROM sales
)
SELECT rep, sale_day, amount
FROM ranked
WHERE rn = 1;
Pick the function by what a tie should do. "Exactly one row per group": ROW_NUMBER. "The top two, ties kept": RANK() … WHERE rnk <= 2 can return more than two rows, and that is honest when two sales share second place.
(Snowflake, BigQuery and DuckDB also have QUALIFY, a WHERE for windows: QUALIFY rn = 1. PostgreSQL, MySQL and SQL Server don't; the CTE works everywhere.)
Try it: put the filter in the same query, then the window itself in WHERE, and read what the database says. Then move it into a CTE, press Play, and do the tasks.
Interactive · Filter a window: compute it in a CTE first — try it in the lesson
Takeaway: windows run after WHERE, GROUP BY and HAVING; to filter on one, compute it in a CTE and filter the CTE.
Now choose the tool yourself. A rough guide:
OVER (PARTITION BY …).ORDER BY and a frame inside OVER.Both can work together: a window runs after GROUP BY, so it can rank the groups. RANK() OVER (ORDER BY SUM(amount) DESC) with GROUP BY rep ranks the reps by their totals.
The playground knows both tables and understands SELECT, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, WITH, CASE, and the window functions of this lesson. Each task says which columns to show.
Interactive · Window functions playground — try it in the lesson
If you get an error, read it: it names the problem, and the usual fixes are a CTE around a window you want to filter, a GROUP BY column you forgot, or 100.0 instead of 100.
Takeaway: GROUP BY makes one row per group; a window keeps every row; together, a window can rank or compare the groups GROUP BY made.
Work out each answer from the two tables (the playground above shows them: Show the tables), then check. A hint costs nothing.
SELECT rep, SUM(amount) OVER (PARTITION BY rep) AS total FROM sales;sales, highest first: 700, 650, 600, 550, 500, 500, 450, 450, … What does RANK() OVER (ORDER BY amount DESC) give Chloe's 450?SUM(opened) OVER (ORDER BY day) show on 6 March?AVG(opened) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)?SUM(amount) OVER (ORDER BY sale_day) show on Ben's 3 March row?WITH r AS (SELECT region, amount, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk FROM sales) SELECT * FROM r WHERE rnk <= 2;Six questions on the whole lesson.
Question 1. Why does `SELECT …, ROW_NUMBER() OVER (…) AS rn FROM sales WHERE rn = 1` fail?
Question 2. What does `LAG(opened) OVER (ORDER BY day)` return on the first day?
Question 3. Two rows share the same `sale_day`. With `SUM(amount) OVER (ORDER BY sale_day)` and no frame written, what do they get?
Question 4. `AVG(opened) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)`. How many rows does the first row's frame hold?
Question 5. Which are true of window functions? (pick all that apply)
Question 6. You need the three biggest sales per region, keeping days that tie for third. Which works?
Workshopy shares this lesson under CC BY 4.0. Anyone may use and change it, with credit to you. Anyone can learn it here, free. A copy in your workspace is yours to change and run live; your students never need an account.
Version 1Current
· First published
Copies are made from the current version. A copy you already have doesn’t change.
WorkshopyOfficial10 stepsQuiz9 interactiveDiagram
gitversion controlbeginners
WorkshopyOfficial16 steps2 quizzes15 interactiveDiagram
workshopyproduct touronboarding
WorkshopyOfficial10 steps2 quizzes10 interactive
data-literacystatisticsmean and median
WorkshopyOfficial11 steps2 quizzes10 interactive
data-literacycorrelationcausation
WorkshopyOfficial9 steps2 quizzes7 interactive
pythonpandasdataframe
WorkshopyOfficial10 steps2 quizzes9 interactive
excelsumifcountif
Comments
No comments yet. Be the first to say how it went.