A window function computes a value across a group of rows without collapsing them into a single row. That is the whole difference from an aggregate. A GROUP BY turns ten sales into one number, while a window function turns those same ten sales into ten rows, and each row carries a figure worked out from the rows around it. This is the feature that made practical reporting possible in SQL, and it has been part of the SQL standard since SQL:2003.
This is the first lesson in the Window Functions module, and the next lesson in this module is about "How Do RANK, DENSE_RANK and ROW_NUMBER Differ?".
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. Every report in this module is built on that data.
Revenue is never stored. It is computed from the quantity, the unit price, and any discount, so the grain of the reporting is one row for each sale. Change a price or a discount and every figure in every report moves with it, because nothing is kept as a fixed total.
Here is the schema that the reporting runs on. There are four dimension tables, the target table, the customer master from module 08, and the fact table itself. The sales table holds the facts, and the tables around it describe them.
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
);There are four regions, seven reps, nine product lines, and a monthly target for every region. The targets live in region_targets, one row for each region and each month, and they give you something to measure the sales against.
The customer master is the one from module 08, and the two institutional accounts join it here. The insert is written as INSERT OR REPLACE, so the seed can run twice without failing. That is the upsert from module 35 in its simplest form. Both of the new rows carry the institutional segment, which is what makes them easy to pick out later.
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 here is the trading itself. There are thirty-two sales, and each one is a rep, a customer, a product, a date, and a price. The quantity and the discount travel with every sale, so the revenue of a single row can be rebuilt at any time.
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, which is the total revenue per rep, is one row for each rep. Seven rows, one per rep, and the individual sales have gone.
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.6It is correct, and it is useless for the question the sales director actually asks, which is "how is Ada Whitfield doing against her own total?" The answer has to show the sale and the total side by side.
The GROUP BY has thrown away the individual sales, so the comparison is impossible. A window function keeps every sale and adds the number beside it.
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.5Every sale row survives, and each one now carries the total for its rep. There are six rows for Ada, five for Bruno, and the same total repeated down each block. That repetition is the signature of a window function. Nothing is grouped away, so you can still read the sale date and the revenue on every row.
What do PARTITION BY and ORDER BY mean inside the OVER?
Two clauses define the window, and they mean exactly what they say.
PARTITION BYdivides the rows into groups and computes the window separately inside each group. It is the window'sGROUP BY.ORDER BYsorts the rows inside each partition before the function is applied. It is the window'sORDER BY, and it is what makes the cumulative functions possible.
Partitioning by the region instead of by the rep gives each sale its region's total. The OVER clause holds the specification, and the expression in front of it is the function that does the work.
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.4The North region carries two reps, and it totals 6192.40. East has one rep whose total is 2955. Every North sale repeats the 6192.40, and every East sale repeats the 2955, whatever the sale itself is worth.
The partition decides whose numbers are related, and choosing the wrong partition is the most common window-function mistake there is. A total per rep and a total per region are different questions, and the partition is what tells the engine which one you are asking.
What does the ORDER BY inside the OVER change?
It turns the window from "all the rows in the partition" into "the rows up to this one", and that 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.9The total climbs from 320 to Ada's final 2918.90, and the last row matches the GROUP BY answer exactly. That is a useful check that the two techniques agree. If the last row of a running total and the plain aggregate ever disagree, you have made a mistake in the window.
What is a named window?
When the same window specification appears in several expressions, you name it and you refer to it. The WINDOW clause goes after the WHERE and before the 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.9The definition appears once, and it is referenced by name. That makes the query far easier to change, because adding an ORDER BY to the window changes every expression that uses it at the same time. You write OVER w in each expression, and the engine looks up what w means.
A named window can also be copied and modified with a second definition that adds to it, and that is how you share a common PARTITION BY across several different orderings. The second name starts from the first one and changes only the part you care about.
What if you want a window over everything?
You leave the partition off. An empty OVER () applies the function to the whole result set, and that 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
----------------------
32There is one row, and it carries the count of all thirty-two sales. The DISTINCT is what leaves a single row behind, because the window on its own would repeat the count on every sale.
Without the DISTINCT the query would return the same number thirty-two times, because a window function evaluates once for every input row. That 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 of them live in different query levels, it stays correct even when you filter the rows that you display.
Quick reference
- A window function computes across rows without collapsing them, and a
GROUP BYcollapses them. PARTITION BYdivides the rows into groups, andORDER BYsorts inside each group before the computation.- You omit
PARTITION BYfor a window over the whole result set. - The
WINDOWclause names a specification once, and it must come after theWHEREand before theORDER BY. - Window functions have been standard SQL since the year 2003, and every major engine supports them.
Try it yourself: 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.