Some questions need a detail row and a group-level number side by side: each day’s revenue and its share of the store’s month, each transaction and the balance after it, each reading and the change since the previous one. GROUP BY can’t do that, because it replaces the detail rows with one row per group. Window functions compute over a set of related rows and write the result onto every row, without collapsing anything.
They’re a staple of SQL interviews for analysts and backend engineers alike. Everything below ran on PostgreSQL 16.15; the running-total tie example was also checked in SQLite 3.51, which gives the same answer.
The same sum, collapsed and not
CREATE TABLE daily_sales (
store text NOT NULL, day date NOT NULL, revenue numeric(10,2) NOT NULL,
PRIMARY KEY (store, day)
);
INSERT INTO daily_sales VALUES
('Leeds','2026-09-01',120), ('Leeds','2026-09-02',80),
('Leeds','2026-09-03',150), ('Leeds','2026-09-04',90),
('York','2026-09-01',260), ('York','2026-09-02',220),
('York','2026-09-04',180);York was closed on 3 September, so it has no row for that day. That gap matters later.
GROUP BY gives one row per store:
SELECT store, sum(revenue) FROM daily_sales GROUP BY store;
-- Leeds | 440.00
-- York | 660.00The same sum with OVER keeps all seven rows and attaches the store total to each:
SELECT store, day, revenue,
sum(revenue) OVER (PARTITION BY store) AS store_total,
round(100 * revenue / sum(revenue) OVER (PARTITION BY store), 1) AS pct_of_store
FROM daily_sales
ORDER BY store, day; store | day | revenue | store_total | pct_of_store
-------+------------+---------+-------------+--------------
Leeds | 2026-09-01 | 120.00 | 440.00 | 27.3
Leeds | 2026-09-02 | 80.00 | 440.00 | 18.2
Leeds | 2026-09-03 | 150.00 | 440.00 | 34.1
Leeds | 2026-09-04 | 90.00 | 440.00 | 20.5
York | 2026-09-01 | 260.00 | 660.00 | 39.4
York | 2026-09-02 | 220.00 | 660.00 | 33.3
York | 2026-09-04 | 180.00 | 660.00 | 27.3PARTITION BY store says “compute separately for each store”. Without it, OVER () means the whole result set is one window. The alternative without window functions is joining the table to a grouped subquery of itself, which works but is longer and easier to get wrong.
Adding ORDER BY: running totals and neighbours
Put ORDER BY inside the window and aggregates become cumulative. Navigation functions such as lag and lead look at neighbouring rows in that order:
SELECT store, day, revenue,
sum(revenue) OVER (PARTITION BY store ORDER BY day) AS running_total,
revenue - lag(revenue) OVER (PARTITION BY store ORDER BY day) AS change_vs_prev
FROM daily_sales
ORDER BY store, day; store | day | revenue | running_total | change_vs_prev
-------+------------+---------+---------------+----------------
Leeds | 2026-09-01 | 120.00 | 120.00 |
Leeds | 2026-09-02 | 80.00 | 200.00 | -40.00
Leeds | 2026-09-03 | 150.00 | 350.00 | 70.00
Leeds | 2026-09-04 | 90.00 | 440.00 | -60.00
York | 2026-09-01 | 260.00 | 260.00 |
York | 2026-09-02 | 220.00 | 480.00 | -40.00
York | 2026-09-04 | 180.00 | 660.00 | -40.00The running total restarts for each store because of the partition. lag returns NULL on each store’s first day, since there’s no previous row; lag(revenue, 1, 0) supplies a default instead. Note York’s last row: change_vs_prev compares 4 September with 2 September, because lag means “previous row”, not “previous day”. If the question is day-over-day, missing days need filling first, typically by generating a calendar with generate_series and left joining sales to it.
The ORDER BY inside OVER only controls the window calculation. It doesn’t sort the output; that’s what the outer ORDER BY is for.
Frames decide which rows feed the calculation
When a window has ORDER BY, each row’s calculation uses a frame: a subset of the partition relative to the current row. You can spell it out. A three-row moving average:
avg(revenue) OVER (PARTITION BY store ORDER BY day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)versus a three-day moving average:
avg(revenue) OVER (PARTITION BY store ORDER BY day
RANGE BETWEEN interval '2 days' PRECEDING AND CURRENT ROW) store | day | revenue | avg_3_rows | avg_3_days
-------+------------+---------+------------+------------
Leeds | 2026-09-03 | 150.00 | 116.67 | 116.67
Leeds | 2026-09-04 | 90.00 | 106.67 | 106.67
York | 2026-09-02 | 220.00 | 240.00 | 240.00
York | 2026-09-04 | 180.00 | 220.00 | 200.00They agree everywhere except York on 4 September. ROWS counts physical rows, so it reaches back to 1 September, three rows ago, and averages 260, 220 and 180. RANGE with an interval counts values of day, so it covers 2 to 4 September and averages only 220 and 180. Which is right depends on whether the business means “last three trading days” or “last three calendar days”. RANGE with an interval offset has been supported since PostgreSQL 11.
The default frame and tied rows
If you write ORDER BY and no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. “Current row” in RANGE mode includes every row with the same ORDER BY value, called peers. On data with duplicates in the sort key, that changes running totals:
CREATE TABLE ledger (id int PRIMARY KEY, posted date, amount int);
INSERT INTO ledger VALUES (1,'2026-09-01',100), (2,'2026-09-02',-30),
(3,'2026-09-02',50), (4,'2026-09-03',-20);
SELECT id, posted, amount,
sum(amount) OVER (ORDER BY posted) AS default_frame,
sum(amount) OVER (ORDER BY posted, id) AS with_tiebreak,
sum(amount) OVER (ORDER BY posted ROWS UNBOUNDED PRECEDING) AS rows_frame
FROM ledger ORDER BY posted, id; id | posted | amount | default_frame | with_tiebreak | rows_frame
----+------------+--------+---------------+---------------+------------
1 | 2026-09-01 | 100 | 100 | 100 | 100
2 | 2026-09-02 | -30 | 120 | 70 | 70
3 | 2026-09-02 | 50 | 120 | 120 | 120
4 | 2026-09-03 | -20 | 100 | 100 | 100With the default frame, rows 2 and 3 both show 120, because both 2 September entries are peers and both are included. A bank statement wants “balance after each transaction”, which is 70 then 120. Adding id as a tiebreaker makes each row unique in the ordering, so the running total advances row by row. ROWS UNBOUNDED PRECEDING without a tiebreaker also advances per row, but the order between the two tied rows isn’t guaranteed, so a statement could show 50 first one day and -30 first the next. The tiebreaker is the real fix.
The same default frame causes the last_value surprise: last_value(revenue) OVER (PARTITION BY store ORDER BY day) returns each row’s own revenue, because the frame ends at the current row. For Leeds it returned 120, 80, 150, 90 instead of 90 four times. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or flip the order and use first_value.
Where windows run, and combining them with GROUP BY
Window functions are evaluated after WHERE, GROUP BY and HAVING, and before the final ORDER BY and LIMIT. Two consequences:
- You can’t filter on them in
WHERE. Wrap the query in a subquery or CTE and filter outside. The ranking guide shows this pattern for top-N per group. - You can window over aggregates. Each store’s share of all revenue:
SELECT store, sum(revenue) AS total,
round(100 * sum(revenue) / sum(sum(revenue)) OVER (), 1) AS pct_of_all
FROM daily_sales
GROUP BY store;
-- Leeds | 440.00 | 40.0
-- York | 660.00 | 60.0sum(sum(revenue)) OVER () reads oddly but is correct: the inner sum is the per-store aggregate from GROUP BY, and the outer sum ... OVER () adds those up across the grouped rows.
The WINDOW clause lets you name a definition once (WINDOW w AS (PARTITION BY store ORDER BY day)) and reuse it as OVER w, which keeps queries with several window columns readable. Performance-wise, each distinct window ordering needs its rows sorted; an index matching (store, day) can supply that order, and several functions sharing one window share one sort.
Interview practice: detecting a drop in daily active users
A table daily_active(app text, day date, users int) has one row per app per day, with occasional missing days when the pipeline failed. Product wants every day where an app’s users fell more than 30% compared with the previous calendar day, and a 7-day moving average for context. Write the query and point out the traps.
Answer
WITH calendar AS (
SELECT a.app, d::date AS day
FROM (SELECT DISTINCT app FROM daily_active) a
CROSS JOIN generate_series(date '2026-09-01', date '2026-09-30', interval '1 day') AS d
),
filled AS (
SELECT c.app, c.day, da.users
FROM calendar c
LEFT JOIN daily_active da ON da.app = c.app AND da.day = c.day
),
scored AS (
SELECT app, day, users,
lag(users) OVER w AS prev_users,
avg(users) OVER (w ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM filled
WINDOW w AS (PARTITION BY app ORDER BY day)
)
SELECT app, day, users, prev_users, round(avg_7d) AS avg_7d
FROM scored
WHERE users < prev_users * 0.7
ORDER BY app, day;The calendar step matters most. Without it, lag returns the previous row, so after a missing day it compares with two days earlier and calls it “yesterday”. With the calendar, a missing day appears with NULL users, users < prev_users * 0.7 is unknown for it and for the day after, and those rows drop out instead of producing false alarms. That’s probably right, but I’d confirm whether product wants missing days flagged separately, since a pipeline failure might be the more urgent signal.
(w ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) reuses the named window and adds a frame. Because the calendar guarantees one row per day, ROWS and a 6-day RANGE are equivalent here, and avg skips NULL days rather than treating them as zero. The filter has to be outside the CTE because window results aren’t available in the WHERE of the query that computes them. The first day per app has no previous value and never matches, which is the correct behaviour.