A window function computes a value across a set of rows without collapsing them. That is the whole difference from an aggregate: GROUP BY turns ten sales into one number, while a window function turns ten sales into ten numbers, each carrying a figure calculated from its neighbours. It is the feature that made reporting in SQL practical, and it has been part of the standard since SQL:2003.
What do window functions add to aggregates?
The furniture sales business in this module has four regions, seven sales representatives and thirty-two sales over three months. Revenue is never stored: it is computed from the quantity, the unit price and any discount, so the reporting grain is one row per sale. Here is the schema the reporting runs on — four dimension tables, a target table, the customer master from module 08, and the fact table itself:
CREATE TABLE regions (
region_id INTEGER PRIMARY KEY,
region_name TEXT NOT NULL
);
CREATE TABLE reps (
rep_id INTEGER PRIMARY KEY,
rep_name TEXT NOT NULL,
region_id INTEGER NOT NULL REFERENCES regions(region_id)
);
CREATE TABLE product_lines (
product_id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL,
category TEXT NOT NULL
);
CREATE TABLE region_targets (
region_id INTEGER NOT NULL REFERENCES regions(region_id),
month_key TEXT NOT NULL,
target_revenue REAL NOT NULL
);
CREATE TABLE sales (
sale_id INTEGER PRIMARY KEY,
rep_id INTEGER NOT NULL REFERENCES reps(rep_id),
customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
product_id INTEGER NOT NULL REFERENCES product_lines(product_id),
sale_date TEXT NOT NULL,
quantity INTEGER NOT NULL,
unit_price REAL NOT NULL,
discount REAL NOT NULL DEFAULT 0
);
Four regions, seven reps, nine product lines and a monthly target for every region. The customer master is the one from module 08, and two institutional accounts join it here — the insert is written as INSERT OR REPLACE so the seed can be run twice without failing, which is the upsert from module 35 in its simplest form:
INSERT INTO regions (region_id, region_name) VALUES
(1, 'East'),
(2, 'North'),
(3, 'South'),
(4, 'West');
INSERT INTO reps (rep_id, rep_name, region_id) VALUES
(1, 'Ada Whitfield', 2),
(2, 'Bruno Kessler', 2),
(3, 'Carla Nunes', 3),
(4, 'Dev Patel', 3),
(5, 'Elif Demir', 4),
(6, 'Farid Haddad', 4),
(7, 'Greta Vogel', 1);
INSERT INTO product_lines (product_id, product_name, category) VALUES
(1, 'Task Chair', 'furniture'),
(2, 'Standing Desk', 'furniture'),
(3, 'Filing Cabinet', 'storage'),
(4, 'Bookshelf', 'storage'),
(5, 'Desk Lamp', 'lighting'),
(6, 'Rug', 'soft goods'),
(7, 'Monitor Arm', 'accessories'),
(8, 'Cable Kit', 'accessories'),
(9, 'Desk Mat', 'accessories');
INSERT INTO region_targets (region_id, month_key, target_revenue) VALUES
(1, '2025-03', 500.0),
(1, '2025-04', 1300.0),
(1, '2025-05', 950.0),
(2, '2025-03', 2800.0),
(2, '2025-04', 1000.0),
(2, '2025-05', 1600.0),
(3, '2025-03', 1500.0),
(3, '2025-04', 1500.0),
(3, '2025-05', 1500.0),
(4, '2025-03', 1200.0),
(4, '2025-04', 2600.0),
(4, '2025-05', 950.0);
INSERT OR REPLACE INTO customers (customer_id, full_name, email, city, signed_up_on, segment) VALUES
(7, 'Harrow and Sons', '[email protected]', 'Leeds', '2025-02-01', 'institutional'),
(8, 'Kestrel Facilities', '[email protected]', 'Bristol', '2025-02-20', 'institutional');
And the quarter of trading itself - thirty-two sales, each one a rep, a customer, a product, a date and a price:
INSERT INTO sales (sale_id, rep_id, customer_id, product_id, sale_date, quantity, unit_price, discount) VALUES
(1, 1, 2, 5, '2025-03-03', 2, 160, 0),
(2, 1, 4, 7, '2025-03-05', 3, 354, 0.05),
(3, 2, 7, 2, '2025-03-07', 3, 280, 0),
(4, 3, 8, 1, '2025-03-10', 3, 285, 0.1),
(5, 4, 7, 3, '2025-03-12', 3, 260, 0),
(6, 5, 1, 6, '2025-03-14', 3, 250, 0),
(7, 6, 8, 5, '2025-03-18', 2, 256, 0),
(8, 7, 7, 4, '2025-03-20', 1, 480, 0.05),
(9, 1, 3, 1, '2025-03-24', 1, 285, 0),
(10, 2, 8, 8, '2025-03-26', 2, 300, 0),
(11, 3, 7, 5, '2025-04-02', 3, 128, 0),
(12, 7, 5, 2, '2025-04-11', 3, 280, 0),
(13, 5, 1, 3, '2025-04-13', 3, 195, 0),
(14, 1, 3, 6, '2025-04-14', 2, 120, 0),
(15, 2, 8, 8, '2025-04-16', 3, 206.5, 0),
(16, 3, 6, 4, '2025-04-18', 2, 120, 0),
(17, 4, 7, 9, '2025-04-21', 3, 240, 0),
(18, 4, 5, 2, '2025-04-22', 3, 119, 0),
(19, 5, 2, 1, '2025-04-24', 3, 361, 0.25),
(20, 6, 1, 1, '2025-04-25', 3, 200, 0.05),
(21, 6, 3, 3, '2025-04-26', 3, 325, 0),
(22, 7, 4, 5, '2025-04-28', 3, 192, 0),
(23, 1, 6, 3, '2025-05-02', 3, 195, 0),
(24, 2, 2, 5, '2025-05-05', 4, 176, 0),
(25, 3, 5, 6, '2025-05-07', 3, 250, 0.3),
(26, 4, 4, 9, '2025-05-09', 3, 118, 0.1),
(27, 5, 6, 4, '2025-05-12', 2, 240, 0),
(28, 6, 1, 9, '2025-05-14', 3, 120, 0),
(29, 7, 3, 1, '2025-05-16', 3, 361, 0),
(30, 1, 2, 4, '2025-05-19', 2, 240, 0),
(31, 2, 4, 6, '2025-05-21', 3, 200, 0.15),
(32, 3, 6, 3, '2025-05-23', 2, 325, 0.1);
The plain aggregate answer — total revenue per rep — is one row per rep:
SELECT r.rep_name, ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)), 2) AS revenue
FROM sales s JOIN reps r ON r.rep_id = s.rep_id
GROUP BY r.rep_name
ORDER BY revenue DESC;
rep_name revenue
------------- -------
Bruno Kessler 3273.5
Greta Vogel 2955.0
Ada Whitfield 2918.9
Elif Demir 2627.25
Carla Nunes 2503.5
Farid Haddad 2417.0
Dev Patel 2175.6
Correct, and useless for the question the sales director actually asks: "how is Ada Whitfield doing against her own total?" The GROUP BY has thrown away the individual sales, so the comparison is impossible. A window function keeps them:
SELECT r.rep_name, s.sale_date,
ROUND(s.quantity * s.unit_price * (1 - s.discount), 2) AS revenue,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)) OVER (PARTITION BY r.rep_id), 2) AS rep_total
FROM sales s JOIN reps r ON r.rep_id = s.rep_id
ORDER BY r.rep_name, s.sale_date
LIMIT 10;
rep_name sale_date revenue rep_total
------------- ---------- ------- --------
Ada Whitfield 2025-03-03 320.0 2918.9
Ada Whitfield 2025-03-05 1008.9 2918.9
Ada Whitfield 2025-03-24 285.0 2918.9
Ada Whitfield 2025-04-14 240.0 2918.9
Ada Whitfield 2025-05-02 585.0 2918.9
Ada Whitfield 2025-05-19 480.0 2918.9
Bruno Kessler 2025-03-07 840.0 3273.5
Bruno Kessler 2025-03-26 600.0 3273.5
Bruno Kessler 2025-04-16 619.5 3273.5
Bruno Kessler 2025-05-05 704.0 3273.5
Every sale row survives, and each one now carries the total for its rep. Six rows for Ada, five for Bruno, and the same total repeated down each block — that repetition is the signature of a window function.
What do PARTITION BY and ORDER BY mean inside OVER?
Two clauses define the window, and they mean exactly what they say.
- PARTITION BY divides the rows into groups and computes the window separately within each group. It is the window's GROUP BY.
- ORDER BY sorts the rows within each partition before the function is applied. It is the window's ORDER BY, and it is what makes cumulative functions possible.
Partitioning by region instead of by rep gives each sale its region's total:
SELECT rg.region_name, r.rep_name, s.sale_date,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)) OVER (PARTITION BY rg.region_id), 2) AS region_total
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN regions rg ON rg.region_id = r.region_id
ORDER BY rg.region_name, s.sale_date
LIMIT 8;
region_name rep_name sale_date region_total
----------- ------------- ---------- -----------
East Greta Vogel 2025-03-20 2955.0
East Greta Vogel 2025-04-11 2955.0
East Greta Vogel 2025-04-28 2955.0
East Greta Vogel 2025-05-16 2955.0
North Ada Whitfield 2025-03-03 6192.4
North Ada Whitfield 2025-03-05 6192.4
North Bruno Kessler 2025-03-07 6192.4
North Ada Whitfield 2025-03-24 6192.4
The North region carries two reps and totals 6192.40, while East has one rep whose total is 2955. The partition decides whose numbers are related — and choosing the wrong partition is the most common window-function mistake there is.
What does ORDER BY inside OVER change?
It turns the window from "all rows in the partition" into "rows up to this one", which is how a running total is built:
SELECT r.rep_name, s.sale_date,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)) OVER (PARTITION BY r.rep_id ORDER BY s.sale_date), 2) AS running_total
FROM sales s JOIN reps r ON r.rep_id = s.rep_id
WHERE r.rep_id = 1
ORDER BY s.sale_date;
rep_name sale_date running_total
------------- ---------- -------------
Ada Whitfield 2025-03-03 320.0
Ada Whitfield 2025-03-05 1328.9
Ada Whitfield 2025-03-24 1613.9
Ada Whitfield 2025-04-14 1853.9
Ada Whitfield 2025-05-02 2438.9
Ada Whitfield 2025-05-19 2918.9
The total climbs from 320 to Ada's final 2918.90, and the last row matches the GROUP BY answer exactly — a useful check that the two techniques agree.
What is a named window?
When the same window specification appears in several expressions, name it and refer to it. The WINDOW clause goes after WHERE and before ORDER BY:
SELECT r.rep_name, s.sale_date,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)) OVER w, 2) AS rep_total
FROM sales s JOIN reps r ON r.rep_id = s.rep_id
WINDOW w AS (PARTITION BY r.rep_id)
ORDER BY r.rep_name, s.sale_date
LIMIT 6;
rep_name sale_date rep_total
------------- ---------- --------
Ada Whitfield 2025-03-03 2918.9
Ada Whitfield 2025-03-05 2918.9
Ada Whitfield 2025-03-24 2918.9
Ada Whitfield 2025-04-14 2918.9
Ada Whitfield 2025-05-02 2918.9
Ada Whitfield 2025-05-19 2918.9
The definition appears once and is referenced by name, which makes the query far easier to change — adding an ORDER BY to the window changes every expression that uses it at the same time. A named window can also be copied and modified with a second definition that adds to it, which is how you share a common PARTITION BY across several different orderings.
What if you want a window over everything?
Leave the partition off. An empty OVER () applies the function to the entire result set, which is how you get a grand total onto every row:
SELECT DISTINCT COUNT(*) OVER () AS rows_in_the_sale_table FROM sales;
rows_in_the_sale_table
----------------------
32
One row, carrying the count of all thirty-two sales. Without the DISTINCT the query would return the same number thirty-two times, because a window function evaluates once per input row — which is a useful reminder that the window does not collapse anything. The same trick gives you a company-wide total to divide by, and because the window is computed before the outer WHERE filter is applied — when the two live in different query levels — it stays correct even when you filter the rows you display.
Quick reference
- A window function computes across rows without collapsing them; a GROUP BY collapses them.
- PARTITION BY divides the rows into groups; ORDER BY sorts within each group before computing.
- Omit PARTITION BY for a window over the whole result set.
- The WINDOW clause names a specification once; it must come after WHERE and before ORDER BY.
- Window functions are standard SQL since 2003 and are supported by every major engine.
Window drill: using the sales table, write a query that lists every sale with the total revenue of its rep, the total revenue of its region, and the total revenue of the whole company — then explain in one sentence why each of the three needs a different partition.