On this page
Before you start
This lesson uses ideas from these earlier lessons. Read them first if any idea is new to you.
01Why interviewers ask this
"Rank the dishes in each restaurant." "Show the total so far, day by day." "How much did revenue change since yesterday?" These questions need a number that depends on other rows, and they still want every row in the answer. Window functions are the standard tool, and using them well is a sign of an SDE-2 level answer.
A weak answer writes a join of the table with itself, or a subquery that runs for every row. It gets slow and hard to read. It also gets ties wrong. A strong answer uses a window function, says which tie rule it picked, and says what the first row shows.
At SDE-2 level you should write ranks, running totals and LAG. At Senior level you should also choose the frame on purpose and know where a window runs in the query.
02The idea in plain words
Look at a cricket scoreboard. Each player has a row with the runs he or she scored. Next to each row is the team total, and the rank of the player. The rows did not shrink to one line for the team. The extra numbers sit beside each row.
GROUP BY collapses the rows of a group into one row. A window function does not. It keeps every row and adds a value computed over a set of related rows, called the window. The syntax is function() OVER (PARTITION BY ... ORDER BY ... frame):
PARTITION BYsplits the rows into separate windows, likeGROUP BYbut without collapsing. Leave it out and the whole result is one window.ORDER BYputs the rows of each window in order.- The frame says which rows around the current row the function can see. We come to it below.
Aggregates as windows
Any aggregate (SUM, AVG, COUNT, MIN, MAX) can be a window function. With empty brackets it sees all rows. We look at 5 delivered orders: each amount, the total next to it, and its share of the total.
SELECT id, amount, SUM(amount) OVER () AS total, ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS share_percent
FROM orders WHERE status = 'delivered' AND id <= 6 ORDER BY id;id amount total share_percent
-- ------ ----- -------------
1 440 1780 24.7
2 200 1780 11.2
3 360 1780 20.2
4 440 1780 24.7
6 340 1780 19.1With PARTITION BY, each restaurant gets its own window. The average of its restaurant sits beside each order.
SELECT id, restaurant_id, amount, ROUND(AVG(amount) OVER (PARTITION BY restaurant_id), 1) AS restaurant_average
FROM orders WHERE status = 'delivered' AND restaurant_id IN (4, 5) ORDER BY restaurant_id, id;id restaurant_id amount restaurant_average
-- ------------- ------ ------------------
2 4 200 275.0
7 4 400 275.0
15 4 240 275.0
23 4 260 275.0
6 5 340 340.0
14 5 340 340.0Ranking: ROW_NUMBER, RANK, DENSE_RANK, NTILE
Three functions number the rows of a window in the order you give. They differ only when two rows tie.
ROW_NUMBER()gives 1, 2, 3, 4 with no repeats. For a tie, the order is not fixed unless you add a tie-break column.RANK()gives the same number to a tie and then skips: 1, 2, 2, 4.DENSE_RANK()gives the same number to a tie and does not skip: 1, 2, 2, 3.NTILE(n)splits the rows intonbuckets of about equal size.
We rank the 8 delivered March orders by amount, biggest first. Two orders have 440, two have 360, and two have 340.
SELECT id, amount,
ROW_NUMBER() OVER (ORDER BY amount DESC, id) AS row_no,
RANK() OVER (ORDER BY amount DESC) AS rank_no,
DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_no,
NTILE(4) OVER (ORDER BY amount DESC, id) AS quarter
FROM orders WHERE status = 'delivered' AND ordered_at < '2026-04-01' AND amount >= 340 ORDER BY amount DESC, id;id amount row_no rank_no dense_no quarter
-- ------ ------ ------- -------- -------
10 640 1 1 1 1
1 440 2 2 2 1
4 440 3 2 2 2
7 400 4 4 3 2
3 360 5 5 4 3
13 360 6 5 4 3
6 340 7 7 5 4
14 340 8 7 5 4Running totals, and what a frame is
A running total adds up the rows from the start to the current row. It is SUM(x) OVER (ORDER BY ...). A window function runs after GROUP BY, so for a total by day, group by day first, with a CTE (lesson 46). We also use date() to cut the time (lesson 43).
WITH daily AS (
SELECT date(ordered_at) AS day, SUM(amount) AS revenue
FROM orders WHERE status = 'delivered' AND ordered_at < '2026-03-16' GROUP BY date(ordered_at)
)
SELECT day, revenue, SUM(revenue) OVER (ORDER BY day) AS running_total FROM daily ORDER BY day;day revenue running_total
---------- ------- -------------
2026-03-02 440 440
2026-03-03 200 640
2026-03-05 360 1000
2026-03-07 440 1440
2026-03-10 340 1780
2026-03-12 400 2180
2026-03-14 210 2390
2026-03-15 300 2690You can also write SUM(SUM(amount)) OVER (ORDER BY ...) in the same query: the inner SUM is the group, and the outer one is the window.
The frame is the part of the window the function sees for the current row. When you give ORDER BY and no frame, the default is "from the first row up to the current row and all its ties". The word is RANGE. Rows with an equal ORDER BY value are called peers, and they get the same running total. If you want strictly one row at a time, write ROWS.
SELECT id, amount,
SUM(amount) OVER (ORDER BY amount) AS default_frame,
SUM(amount) OVER (ORDER BY amount, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM orders WHERE status = 'delivered' AND id IN (1, 4, 7, 6, 14) ORDER BY amount, id;id amount default_frame rows_frame
-- ------ ------------- ----------
6 340 680 340
14 340 680 680
7 400 1080 1080
1 440 1960 1520
4 440 1960 1960Orders 6 and 14 both have 340, and orders 1 and 4 both have 440. With the default frame, peers get the same total. With ROWS the total grows one row at a time. We added id as a tie-break, so the order of the peers is fixed.
LAG and LEAD
LAG(x, n, default) returns the value of x from n rows before the current row, inside the window. LEAD looks forward. If there is no such row, you get the default, or NULL. The first row has no row before it, so its change is NULL.
WITH daily AS (
SELECT date(ordered_at) AS day, SUM(amount) AS revenue
FROM orders WHERE status = 'delivered' AND ordered_at < '2026-03-16' GROUP BY date(ordered_at)
)
SELECT day, revenue, LAG(revenue) OVER (ORDER BY day) AS previous,
revenue - LAG(revenue) OVER (ORDER BY day) AS change,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY day)) / LAG(revenue) OVER (ORDER BY day), 1) AS change_percent
FROM daily ORDER BY day;day revenue previous change change_percent
---------- ------- -------- ------ --------------
2026-03-02 440 NULL NULL NULL
2026-03-03 200 440 -240 -54.5
2026-03-05 360 200 160 80.0
2026-03-07 440 360 80 22.2
2026-03-10 340 440 -100 -22.7
2026-03-12 400 340 60 17.6
2026-03-14 210 400 -190 -47.5
2026-03-15 300 210 90 42.9Note: "the row before" means the row before in the result, not yesterday. Days with no rows are not in the table, so their gap is skipped.
FIRST_VALUE, LAST_VALUE, NTH_VALUE and the frame trap
FIRST_VALUE(x) is the first row of the frame, LAST_VALUE(x) is the last, and NTH_VALUE(x, n) is the n-th. The trap: with an ORDER BY the default frame ends at the current row, so LAST_VALUE returns the current row itself. Widen the frame to see the true last row.
SELECT id, restaurant_id, amount,
FIRST_VALUE(amount) OVER w AS first_amount,
LAST_VALUE(amount) OVER w AS wrong_last,
LAST_VALUE(amount) OVER (PARTITION BY restaurant_id ORDER BY ordered_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount,
NTH_VALUE(amount, 2) OVER (PARTITION BY restaurant_id ORDER BY ordered_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_amount
FROM orders WHERE status = 'delivered' AND restaurant_id = 1
WINDOW w AS (PARTITION BY restaurant_id ORDER BY ordered_at)
ORDER BY ordered_at;id restaurant_id amount first_amount wrong_last last_amount second_amount
-- ------------- ------ ------------ ---------- ----------- -------------
1 1 440 440 440 400 640
10 1 640 440 640 400 640
16 1 400 440 400 400 640NTH_VALUE(amount, 2) is the amount of the 2nd row of the frame. It needs the wide frame too. The last line before ORDER BY is a named window. WINDOW w AS (...) defines the window once, and OVER w uses it, so you do not repeat it.
Frames: moving averages
A frame can name a number of rows: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is the current row and the two rows before it. This gives a moving average of 3 rows. If days are missing, 3 rows are not 3 days. For calendar days, use RANGE with a number, on a number that counts days.
WITH daily AS (
SELECT date(ordered_at) AS day, SUM(amount) AS revenue
FROM orders WHERE status = 'delivered' AND ordered_at < '2026-03-16' GROUP BY date(ordered_at)
)
SELECT day, revenue,
ROUND(AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS last_3_rows,
ROUND(AVG(revenue) OVER (ORDER BY CAST(julianday(day) AS INTEGER) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS last_3_days
FROM daily ORDER BY day;day revenue last_3_rows last_3_days
---------- ------- ----------- -----------
2026-03-02 440 440.0 440.0
2026-03-03 200 320.0 320.0
2026-03-05 360 333.3 280.0
2026-03-07 440 333.3 400.0
2026-03-10 340 380.0 340.0
2026-03-12 400 393.3 370.0
2026-03-14 210 316.7 305.0
2026-03-15 300 303.3 255.0On 7 March the last 3 rows are 3, 5 and 7 March. The last 3 days are 5, 6 and 7 March, and only 5 and 7 March have orders.
CUME_DIST
CUME_DIST() gives the share of rows at or below the current row: a cumulative share, from 0 to 1.
SELECT id, amount, ROUND(CUME_DIST() OVER (ORDER BY amount), 3) AS share_at_or_below
FROM orders WHERE status = 'delivered' AND id IN (2, 8, 11, 9, 7) ORDER BY amount;id amount share_at_or_below
-- ------ -----------------
11 170 0.2
2 200 0.4
8 210 0.6
9 300 0.8
7 400 1.0Where windows run
A window function runs after WHERE, GROUP BY and HAVING, and just before the final SELECT list is made. So you cannot filter on it in WHERE, and you cannot put one inside another. Wrap it in a CTE and filter outside. We want the biggest order of each customer:
SELECT id, amount FROM orders WHERE ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) = 1;Error: misuse of window function ROW_NUMBER()WITH ranked AS (
SELECT id, customer_id, amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, id) AS rn
FROM orders WHERE customer_id IN (1, 2, 3)
)
SELECT id, customer_id, amount FROM ranked WHERE rn = 1 ORDER BY customer_id;id customer_id amount
-- ----------- ------
10 1 640
7 2 400
3 3 360Window, self-join or subquery? The window function reads the table once and is short. A self-join or a correlated subquery says the same thing in more words and may read the table many times. Use a window when each row needs a value from its group or its neighbours. Use GROUP BY when you want one row per group.
In Postgres and MySQL
- The syntax is the same standard. MySQL has window functions only from version 8.0.
QUALIFYfilters on a window value directly. It exists in some other systems, not in Postgres, MySQL or SQLite, so use a CTE.- Postgres and MySQL also let
RANGEuse an interval on a date column, so you can say 2 days instead of a number. In SQLite, order by a number.
Want to check your understanding? Try explaining this idea in your own words. You can continue reading first, or ask the AI tutor a question. Sign up free to ask the tutor
03Pause and predict
Pause. Guess first.
Two orders tie for the 2nd biggest amount (440). The next amount is 400. What number do
RANKandDENSE_RANKgive to the order with 400?
Write your guess before you read on. The answer is in the worked example.
04Worked example
The owner asks three things about March.
Part 1: revenue by day with a running total and a 3-day average. Days are missing in the data, so for "3 days" we use the RANGE frame on a day number, as above. We read 10 days.
WITH daily AS (
SELECT date(ordered_at) AS day, SUM(amount) AS revenue
FROM orders WHERE status = 'delivered' AND ordered_at < '2026-03-21' GROUP BY date(ordered_at)
)
SELECT day, revenue,
SUM(revenue) OVER (ORDER BY day) AS running_total,
ROUND(AVG(revenue) OVER (ORDER BY CAST(julianday(day) AS INTEGER) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS last_3_days
FROM daily ORDER BY day;day revenue running_total last_3_days
---------- ------- ------------- -----------
2026-03-02 440 440 440.0
2026-03-03 200 640 320.0
2026-03-05 360 1000 280.0
2026-03-07 440 1440 400.0
2026-03-10 340 1780 340.0
2026-03-12 400 2180 370.0
2026-03-14 210 2390 305.0
2026-03-15 300 2690 255.0
2026-03-18 640 3330 640.0
2026-03-20 170 3500 405.0Check by hand. The first three days with orders are 2, 3 and 5 March, with 440, 200 and 360. The running total on 5 March is 440 + 200 + 360 = 1,000. The last 3 days of 5 March are 3, 4 and 5 March, and only 3 and 5 March have orders: (200 + 360) / 2 = 280. On 7 March the last 3 days are 5, 6 and 7 March: (360 + 440) / 2 = 400. A 3-row average would give 333.3 there, which is not a 3-day average.
Part 2: each customer's orders in order, with days since the previous order. PARTITION BY customer_id gives each customer a window. The first order of each customer has no previous order, so the gap is NULL.
WITH with_previous AS (
SELECT customer_id, id AS order_id, date(ordered_at) AS day,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS nth_order,
LAG(date(ordered_at)) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS previous_day
FROM orders WHERE customer_id IN (2, 3)
)
SELECT customer_id, order_id, day, nth_order, CAST(julianday(day) - julianday(previous_day) AS INTEGER) AS days_since_previous
FROM with_previous ORDER BY customer_id, day;customer_id order_id day nth_order days_since_previous
----------- -------- ---------- --------- -------------------
2 2 2026-03-03 1 NULL
2 7 2026-03-12 2 9
2 15 2026-04-02 3 21
2 24 2026-05-20 4 48
3 3 2026-03-05 1 NULL
3 9 2026-03-15 2 10
3 13 2026-03-25 3 10
3 18 2026-04-12 4 18Part 3: "the third biggest order". The words are not clear when there is a tie. This is the answer to the predict box. Amounts 640, 440, 440, 400 give:
ROW_NUMBER: the 3rd row is the second order with 440.RANK: ranks are 1, 2, 2, 4. There is no rank 3, and the order with 400 has rank 4.DENSE_RANK: ranks are 1, 2, 2, 3. The order with 400 has rank 3.
The ranking query above printed exactly these numbers. In an interview, ask: "Do ties share a place, and do places skip?"
05Trade-offs
- It solves: per-row numbers that depend on the group or the neighbours, in one pass and in a short query.
- It costs: the database must sort and keep each window, so a window over a huge table can be slow and needs memory. The frame rules are easy to get wrong in silence, as the
LAST_VALUEtrap shows. - I would change it when: the table is huge and the same running total is read all day. Then I store the totals in a summary table (lesson 45). If I only need one row per group, I use
GROUP BYand not a window. - Always say the tie rule and what the first row shows.
06Common misconceptions
"A window function and GROUP BY do the same thing." GROUP BY returns one row per group. A window function keeps every row and adds a value.
"LAST_VALUE gives the last row of the group." With an ORDER BY and no frame, the frame stops at the current row. It returns the current row. Widen the frame.
"I can filter on ROW_NUMBER in WHERE." WHERE runs before the window exists. Put the window in a CTE, then filter rn = 1 outside.
07How to say it in the interview
Say it like this
I use a window function because I need a value beside every row. I say my tie rule: ROW_NUMBER with a tie-break, RANK or DENSE_RANK. I check the first row and the frame, because the default frame stops at the current row.
08Check your understanding
- Which function gives 1, 2, 2, 4 for a tie in second place? Which gives 1, 2, 2, 3?
- Daily revenue is 100, 200, 300, 400. What is the running total on day 4? What is the moving average of the last 3 rows on day 4?
- A teammate writes
LAST_VALUE(amount) OVER (ORDER BY ordered_at)to get the last amount of a restaurant, and every row shows its own amount. Why? What is the fix?
09Answers
Show the answers
RANKgives 1, 2, 2, 4.DENSE_RANKgives 1, 2, 2, 3.- Running total: 100 + 200 + 300 + 400 = 1,000. Moving average: (200 + 300 + 400) / 3 = 300.
WITH d(day, revenue) AS (VALUES (1, 100), (2, 200), (3, 300), (4, 400))
SELECT day, SUM(revenue) OVER (ORDER BY day) AS running_total, AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average FROM d;day running_total moving_average
--- ------------- --------------
1 100 100.0
2 300 150.0
3 600 200.0
4 1000 300.0- The default frame ends at the current row, so the last value of the frame is the current row. Add
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, as in the example above.
10Summary card: SQL window functions
One-page summary
GROUP BYcollapses rows. A window functionf() OVER (PARTITION BY ... ORDER BY ... frame)keeps every row and adds a value.- Ranks:
ROW_NUMBER(1, 2, 3, 4; add a tie-break),RANK(1, 2, 2, 4),DENSE_RANK(1, 2, 2, 3),NTILE(n).- Running total:
SUM(x) OVER (ORDER BY day). The default frame isRANGE: ties get the same total. UseROWSfor one row at a time. Group by day first.LAG(x, n, default)andLEAD: the first row has no previous row, so the result is NULL.- Frames:
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW;RANGEwith a number for calendar days.LAST_VALUEneeds a wider frame.- Windows run after
WHERE,GROUP BYandHAVING: filter them in an outer query (rn = 1). No window inside a window.- Trap: the tie rule, and rows are not days when days are missing.
11Sources
- SQLite documentation: window functions (frames,
RANGEwith a number, theWINDOWclause) https://www.sqlite.org/windowfunctions.html (opens in a new tab). - Postgres manual on window functions and frame rules, and the MySQL manual on window functions (version 8.0). Date checked: 29 Oct 2026.
Try what you just learned
Put this idea into practice with a short exercise or a relevant tool. Free to try. No sign-up needed.
More practice. This needs a free account.
- SQL practice (medium): Running total of daily revenue
- SQL practice (medium): Three day moving average of sign-ups
- SQL practice (medium): Day over day change in orders
- SQL practice (medium): Share of revenue by category
- SQL practice (medium): End of day balance of each account
- SQL practice (medium): Average days between orders
- SQL practice (medium): First and last order amount of each customer
- SQL practice (medium): Share of users by number of orders
- SQL practice (medium): Month over month revenue growth
- SQL practice (medium): Top two salaries in each department
- SQL practice (medium): Top two dishes of each restaurant
- SQL practice: the full problem list
What to learn next
Continue learning with the lessons that build on this one.
Have a question about this lesson?
Ask the AI tutor to explain a difficult idea, walk through an example or check your understanding. It uses the lesson to help you work through the topic.
A free account includes 10 AI tutor questions a day. The lesson remains free to read without an account.
All lessons in Module 7: SQL for interviews
Prefer another topic? Browse all 169 lessons.