Window Functions: Beginner

An aggregate function does 2 jobs at once: it computes a value over a set of rows, and it collapses that set into 1 output row. SUM(salary) with nothing else folds the whole table into a single row, and SUM(salary) with GROUP BY department folds it into 1 row per department. The OVER keyword separates the 2 jobs. SUM(salary) OVER () computes the same total, and every input row survives into the output with that total attached as a new column. The set of rows the function reads is its window, which is where the name comes from, and any aggregate becomes a window function the moment OVER follows it.

The lesson follows 1 table through all 5 sections: employees, 6 people in 3 departments, each with a salary and a hire date. Total payroll is 605,000. The table is small enough to add up by hand on purpose, so every window result below can be predicted before the query runs.

The table can answer 3 different questions with SUM, and the clause around it decides which one. Plain SUM(salary) answers how much the company pays in total and returns 1 row. SUM(salary) with GROUP BY department answers what each department costs and returns 3 rows. SUM(salary) OVER () answers how each person's pay sits against the company total and returns 6 rows, one per employee, with 605,000 on every one of them. The 3 tabs below run each version; the row count is the thing to watch.

The repeated 605,000 in the third tab looks wrong to a spreadsheet eye, but it follows directly from how the window is defined. The window for an empty OVER () is every row the query holds at that point, so SUM reads all 6 salaries once, arrives at 605,000, and the engine writes that value into the new column on all 6 rows. Each row's value is computed from the window that row belongs to, and no row borrows the figure from a neighbour. Once the total is on the row, ordinary arithmetic can use it: 100.0 * salary / SUM(salary) OVER () gives Carol 21.5 percent of payroll and Dave 13.2 percent, in the same SELECT list, with no second query.

A query runs its clauses in a fixed order, and window functions have a fixed place in it: after FROM, WHERE, GROUP BY and HAVING have produced their rows, and before the final ORDER BY and LIMIT arrange and trim them. The window therefore holds only the rows that survived WHERE: add WHERE department = 'Sales' and SUM(salary) OVER () returns 170,000 on the 2 Sales rows, because the other 4 were gone before the window formed. A window function also cannot appear in WHERE or HAVING, because those clauses run before its value exists. Filtering on a window result needs an outer query, which section 2 shows with the top-earner-per-department pattern.

The answer depends on where OVER () runs. If it were a second pass over the whole table, the answer would be 1,200; it runs on the rows WHERE left behind, so the answer is 900. This window also needs no sort: 1 pass totals the rows the engine already holds.

The choice between the 2 is the shape of the answer wanted. A report listing departments and their totals is a GROUP BY. A report listing people, each against a total, is a window function. The older route to the second report was a GROUP BY subquery of department totals joined back to employees on department, which costs a second scan of the table. MySQL added OVER in 8.0 and SQLite in 3.25, both in 2018, so every major engine now supports it.

OVER () builds 1 window from every row. PARTITION BY splits it into several, 1 per distinct value of the columns named, and the function runs separately inside each. SUM(salary) OVER (PARTITION BY department) reads the 2 Engineering rows and produces 250,000, the 2 Sales rows and produces 170,000, the 2 Marketing rows and produces 185,000, and every row receives the figure for its own partition. Row count stays at 6. The partition keys do the same work the keys in GROUP BY do, which is why a GROUP BY department query and a PARTITION BY department window always agree on the per-department figures; the difference is what happens to the rows afterwards.

Trace Alice. Her department is Engineering, so her window is the Engineering rows: Alice at 120,000 and Carol at 130,000. SUM over that window is 250,000, AVG is 125,000, and her share of the department is 120,000 divided by 250,000, which is 48 percent. Carol's window is the same 2 rows, so she receives the same 250,000 and 125,000, and a share of 52 percent. Dave in Sales never sees either of them; his window is Dave and Eve, totalling 170,000, and his share of it is 47.1 percent.

This SELECT holds 3 window functions, all over the same partition, and the engine builds the partition once and reuses it for each. pct_of_dept divides a column by a window result, which is allowed in the SELECT list and the final ORDER BY. A window value cannot appear in WHERE, GROUP BY, HAVING or a join condition, because all of them run before the window is computed. It also cannot be nested inside another aggregate.

The animation plays the AVG version clause by clause: 6 rows arrive from FROM, the engine sorts them into 3 partitions by department, computes 1 average inside each, and writes that average onto every row of the partition it came from.

Engineering averages 125,000, Sales 85,000, Marketing 92,500. Each figure appears once per row of its partition. That tells a window average from a GROUP BY average at a glance: the GROUP BY version is 3 rows with no names.

The everyday use of a partitioned aggregate is a gap from the group figure. salary - AVG(salary) OVER (PARTITION BY department) gives Alice minus 5,000 and Carol plus 5,000, Dave minus 5,000 and Eve plus 5,000, Frank plus 2,500 and Grace minus 2,500. It takes 1 expression and no self-join, and the answer lands on the row where the follow-up question, who is paid below their department's average, will be asked. The same shape gives a product's price against its category average, a server's latency against its region's, and a store's sales against its chain's.

PARTITION BY takes a list, and a window forms for each distinct combination. PARTITION BY department, hire_year on a larger payroll table makes 1 window per department per hiring cohort, so an employee is compared with the people hired into the same department in the same year. More columns mean smaller partitions, until every row is alone in a partition of 1 and the aggregate returns the row's own value. That is a useful diagnostic: a window total that equals the detail column on every row means the partition is too fine.

The practice table employees in the platform database has the same columns as the running example and many more rows. The blanks ask for the 2 decisions every partitioned window makes: what to compute, and which rows to compute it over.

Among the distractors, ORDER BY department runs without error and returns a number on every row, which is why a wrong window clause is dangerous: the query succeeds, the column looks plausible, and the figures are a running average across departments in alphabetical order rather than a per-department average. PARTITION BY emp_name also runs, and returns each person's own salary as their average, the partition-of-1 symptom from above.

PARTITION BY decides which rows share a window. ORDER BY inside OVER decides the order of rows within it, and a family of functions depends on that order entirely. ROW_NUMBER() is the first: it walks the window in the order given and hands out 1, 2, 3 and so on, never repeating a number and never skipping one. ROW_NUMBER() OVER (ORDER BY salary DESC) numbers the 6 employees from highest earner to lowest: Carol 1, Alice 2, Frank 3, Eve and Grace at 4 and 5 in some order, Dave 6. The function takes no argument because it reads nothing from the row; the position in the window is the whole output.

Add a partition and the numbering restarts at 1 inside each one. ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) gives Carol 1 and Alice 2 in Engineering, Eve 1 and Dave 2 in Sales, Frank 1 and Grace 2 in Marketing. The partition is formed first, the order is applied within it, and the numbers are assigned last. That sequence (partition, then order, then compute) is the one to picture for every function in the rest of the lesson.

Both columns come from 1 query with no join, and they answer different questions. company_pos is a standing across the company, and dept_pos is a standing within a team. Change the ORDER BY inside the second OVER to hire_date and dept_pos becomes seniority within the department. The partition and the 6 rows stay as they were.

Eve and Grace both earn 90,000. ORDER BY salary DESC puts them next to each other and says nothing about which comes first, so ROW_NUMBER gives one of them 4 and the other 5 by whatever order the engine happened to read the rows in. The same query can return Eve 4 today and Grace 4 after the table is rewritten or the engine picks a different plan. The fix is a tiebreaker: ORDER BY salary DESC, emp_name makes the sequence fully determined, and ORDER BY salary DESC, employee_id does the same with an id that never changes. Omit ORDER BY altogether and ROW_NUMBER still runs, numbering rows in whatever order they arrived, which serves exactly 1 purpose: stamping a unique but meaningless id on rows that have none.

The question ROW_NUMBER exists for is first-per-group: the highest-paid person in each department, the latest order per customer, the most recent reading per sensor. The window numbers the rows and a filter keeps the 1s. The filter cannot sit in the same query's WHERE, because WHERE runs before the number exists, so the numbering query becomes a derived table (a query placed in FROM and given a name) and the filter goes on the outer query.

Change = 1 to <= 2 and the query returns the top 2 per department, which is 6 rows here because every department has 2 people, and would be 2 rows per department on a department of 40. GROUP BY department with MAX(salary) finds the 3 numbers, and getting the names back requires a join from the 3 maxima to the detail rows; the window returns the names directly, and handles top 2, top 5 or top 10 by changing 1 constant.

The puzzle below is the inner query, split into clauses. Two tiles are distractors from the GROUP BY habit, and the grammar fixes the order of the rest.

A second everyday use is pagination. Number the rows with ROW_NUMBER() OVER (ORDER BY hire_date, employee_id) AS row_pos and filter the outer query on row_pos BETWEEN 21 AND 40, and the result is the second page of 20. The order includes a unique column, so page 2 contains the same rows on every request. LIMIT with OFFSET on a non-unique order does not guarantee that.

ROW_NUMBER forces a strict sequence onto rows that may not have one. Eve and Grace earn the same 90,000, and a report saying Eve is 4th and Grace is 5th asserts a difference the data does not contain. The ranking functions give tied rows the same number. There are 2, and they differ only in what happens to the number after a tie. RANK leaves a gap, DENSE_RANK does not.

RANK for a row is 1 plus the count of rows that sort strictly ahead of it. Eve and Grace have 3 people ahead, so both get 4; Dave has 5 people ahead, so he gets 6, and no row carries 5. The gap is deliberate: a rank of 6 says that 5 people out-earn Dave. DENSE_RANK for a row is the count of distinct values at or above its own, so Dave's 80,000 is the 5th distinct salary and he gets 5, which says that 4 pay levels sit above him. Both statements are true about the same table; they answer different questions.

Give Grace a raise to 95,000 in the table and rerun. She ties Frank at 3, Eve drops to a RANK of 5 and a DENSE_RANK of 4, and the row numbers shift by 1. The 3 columns disagree only around ties and agree exactly on a table with no duplicate salaries, which is why a wrong choice of ranking function hides until the first tie arrives in production.

That was RANK, which counts rows ahead. DENSE_RANK counts distinct values instead, and Dave's row is the only one that changes.

Both functions take PARTITION BY the way ROW_NUMBER does. Partitioned on department and still ordered by salary, the ranks restart at 1 in each department, and a tie inside 1 department leaves a gap there and nowhere else. Both functions also need an ordering to mean anything. Without one, every row in the window ties with every other, and all 6 employees are ranked 1.

The 3rd highest salary, as a pay level, is DENSE_RANK() = 3. If 2 people tie for 2nd, RANK jumps to 4 and no row carries 3. The top 3 places, with ties sharing a place, is RANK() <= 3, which is how a podium works. A fixed-length list of exactly 3 rows is ROW_NUMBER() <= 3 with a tiebreaker column.

Sections 2 and 3 used ORDER BY inside OVER with functions that produce positions. Put it on an aggregate and the aggregate changes shape. SUM(salary) OVER (ORDER BY hire_date) no longer returns 1 total for the window; for each row it sums the rows that sort at or before that row, so the result grows as the order advances. That is a running total. The rule underneath is that an ordered window has a default frame, the rows from the start of the window up to the current one, and the Intermediate lesson covers changing it; this section uses the default throughout.

In hire order the payroll grew like this. Dave was hired first, in February 2019, and the payroll at that point was his 80,000. Alice joined in September 2019 and the running figure became 200,000. Frank brought it to 295,000 in April 2020, Eve to 385,000 in November 2021, Carol to 515,000 in June 2022 and Grace to 605,000 in March 2023. The last row's running total equals the SUM(salary) OVER () from section 0, and that holds for every running total: it ends where the grand total is.

The same ORDER BY turns COUNT into a running headcount and AVG into a running average. COUNT(*) OVER (ORDER BY hire_date) returns 1, 2, 3, 4, 5, 6: the headcount after each hire. AVG(salary) OVER (ORDER BY hire_date) returns 80,000, then 100,000, 98,333, 96,250, 103,000 and 100,833: the average pay of the workforce as it stood after each hire, and each value is the running total divided by the running count on the same row. All 3 use the identical frame, and all 3 come from 1 pass over the 6 rows.

Swap hire_date for salary in all 3 windows and the running total becomes cumulative payroll from the lowest earner upward, the column behind a chart that asks what fraction of payroll the lowest-paid half of staff receives. The order column is the axis the running figure moves along, and a date is only the most common choice.

The default frame is defined by value, and rows with equal order values are peers: each one's frame includes all of its peers. With 2 people hired on the same day, both rows show the running total after both hires, and the figure appears to jump by 2 salaries at once. That is right for a question about payroll on a given date and wrong for a question about payroll after each individual hire. A tiebreaker in the ORDER BY (ORDER BY hire_date, employee_id) makes every row its own step, the same remedy section 2 applied to ROW_NUMBER. Dates in the running example are all distinct, so the 6-row table never shows the effect; a production table with 400 hires in a year will.

PARTITION BY and ORDER BY combine in 1 OVER. SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) restarts the running total in each department: Sales goes 80,000 then 170,000, Engineering 120,000 then 250,000, Marketing 95,000 then 185,000. Each partition's last row carries the department total from section 1, and the order inside 1 partition has no effect on any other. This is the shape of month-to-date revenue per store, cumulative spend per customer and a running error count per service.

The blanks below ask for that shape on the practice employees table: the aggregate, and the order the total accumulates in.

Every distractor runs. ORDER BY salary gives a cumulative payroll ordered by pay within each department, ORDER BY emp_name an alphabetical one, and COUNT a running headcount; the query never fails, so the check is reading 1 department's rows and confirming the column climbs in hire order and ends on the department total.