SQL 54 🛢️ Introduction to Window Functions and OVER Clause
A window function computes a value across a set of rows without collapsing them into a single row. An aggregate function with GROUP BY produces one row per group; a window function produces one row per input row, with the computed value attached. This is the difference between “the average salary of the department” and “the average salary of the department, shown next to each employee.” The first is an aggregate. The second is a window function.
This chapter introduces window functions and the OVER clause that defines them. You will learn the syntax, the difference between a window and a group, the PARTITION BY and ORDER BY clauses, the difference between aggregate window functions and ranking window functions, and the frame specification that controls which rows are included in the window.
Key point: A window function is written as function() OVER (window_specification). The window_specification has three parts: PARTITION BY divides the rows into groups, ORDER BY orders the rows within each group, and the frame clause defines which rows are included relative to the current row. The PARTITION BY is optional. The ORDER BY is required for ranking functions and optional for aggregates. The frame clause is optional and has a default.
Why window functions matter
The aggregate limitation. An aggregate function with GROUP BY reduces the rows. “Average salary per department” returns one row per department. The individual employees are gone. Sometimes the goal is to compare each employee to their department average, which requires both the employee row and the average. A window function provides both.
The subquery problem. Before window functions, the comparison required a subquery or a self-join. “Employees earning more than their department average” was written with a correlated subquery or a join to a derived table of averages. The window function expresses it directly: AVG(salary) OVER (PARTITION BY department).
The ranking problem. “The top three products per category” is difficult without window functions. A window function with ROW_NUMBER() or RANK() assigns a rank to each row within its category, and the outer query filters on the rank.
The running total problem. A running total is a value that accumulates over an ordered sequence. “Cumulative sales by day” requires each row to see the rows before it. A window function with an ordered frame computes the running total in a single query.
The row-comparison problem. “The difference between this row and the previous row” is a window function with LAG() or LEAD(). Without it, the query requires a self-join on a sequential key.
The performance problem. A window function is computed in a single pass over the data. The equivalent subquery or self-join may scan the data multiple times. The window function is often faster, and it is always clearer.
a. The OVER clause and the window specification
A window function is a function followed by OVER and a parenthesized specification.
SELECT
employee_id,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;The AVG(salary) is the function. The OVER (PARTITION BY department) is the window specification. The result has one row per employee, with the department average attached to each.
The PARTITION BY divides the rows into groups. The function is computed independently within each group. Without PARTITION BY, the function is computed over all rows.
SELECT
employee_id,
salary,
AVG(salary) OVER () AS overall_avg
FROM employees;The OVER () with an empty specification computes the average over all rows. Every row gets the same value.
The ORDER BY within the window orders the rows within each partition. It is required for ranking functions and for functions that depend on the order.
SELECT
employee_id,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;The ROW_NUMBER() assigns a sequential number within each department, ordered by salary descending. The highest-paid employee in each department gets 1, the second gets 2, and so on.
The PARTITION BY and ORDER BY can be combined with multiple columns.
SELECT
order_id,
customer_id,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS running_total
FROM orders;The running total is computed per customer, ordered by date. Each row sees the rows before it in the same customer’s order sequence.
A window function can be given a name with the WINDOW clause, which is useful when the same specification is used by multiple functions.
SELECT
employee_id,
department,
salary,
AVG(salary) OVER w AS dept_avg,
MAX(salary) OVER w AS dept_max,
MIN(salary) OVER w AS dept_min
FROM employees
WINDOW w AS (PARTITION BY department);The WINDOW w AS (...) defines the specification once, and the functions reference it by name. This is cleaner than repeating the specification.
b. Aggregate window functions vs ranking functions
The window functions fall into two categories: aggregate functions used as window functions, and dedicated window functions.
The aggregate functions—SUM, AVG, MIN, MAX, COUNT—can be used with OVER. They compute the aggregate over the window for each row.
SELECT
employee_id,
salary,
SUM(salary) OVER () AS total,
AVG(salary) OVER () AS average,
COUNT(*) OVER () AS count
FROM employees;The dedicated window functions are ranking, offset, and distribution functions.
| Function | Purpose |
|---|---|
ROW_NUMBER() | Sequential number, no ties |
RANK() | Rank with gaps for ties |
DENSE_RANK() | Rank without gaps |
NTILE(n) | Divide into n buckets |
LAG(col, n) | Value from n rows before |
LEAD(col, n) | Value from n rows after |
FIRST_VALUE(col) | First value in the window |
LAST_VALUE(col) | Last value in the window |
NTH_VALUE(col, n) | nth value in the window |
PERCENT_RANK() | Percentile rank |
CUME_DIST() | Cumulative distribution |
The ROW_NUMBER() always produces a unique number. The RANK() produces the same rank for ties and skips the next rank. The DENSE_RANK() produces the same rank for ties and does not skip.
SELECT
employee_id,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;For salaries [100, 90, 90, 80]:
ROW_NUMBER:1, 2, 3, 4RANK:1, 2, 2, 4DENSE_RANK:1, 2, 2, 3
The LAG and LEAD functions access other rows in the window.
SELECT
order_date,
amount,
LAG(amount) OVER (ORDER BY order_date) AS prev_amount,
LEAD(amount) OVER (ORDER BY order_date) AS next_amount
FROM orders;The LAG gives the previous row’s amount, and the LEAD gives the next row’s amount. The difference between the current and previous is the change.
SELECT
order_date,
amount,
amount - LAG(amount) OVER (ORDER BY order_date) AS change
FROM orders;The NTILE(n) divides the ordered rows into n buckets.
SELECT
employee_id,
salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;The NTILE(4) assigns each row to one of four quartiles. The top quarter gets 1, the next gets 2, and so on.
c. The frame clause
The frame clause defines which rows are included in the window for each row. It is relevant for aggregate functions with an ORDER BY, where the default frame is all rows from the start of the partition to the current row.
SELECT
order_date,
amount,
SUM(amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW defines a frame that includes all rows from the start of the partition to the current row. This is the running total.
The frame can be specified in several ways.
| Frame | Meaning |
|---|---|
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | From start to current |
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING | From current to end |
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING | All rows |
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING | Current ± 1 |
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW | Last 4 rows |
The ROWS keyword specifies physical rows. The RANGE keyword specifies logical ranges based on the ORDER BY value. The RANGE is the default when an ORDER BY is present.
SELECT
order_date,
amount,
SUM(amount) OVER (
ORDER BY order_date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
) AS rolling_7_day
FROM orders;The RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW includes all rows whose order_date is within the last 6 days of the current row. This is a rolling 7-day total.
The default frame depends on the presence of an ORDER BY.
| Window | Default Frame |
|---|---|
No ORDER BY | All rows in the partition |
With ORDER BY | RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
The default frame with an ORDER BY is a running aggregate, not the entire partition. This is a common surprise. To get the entire partition, specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
SELECT
order_date,
amount,
SUM(amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS total
FROM orders;The ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING includes all rows, so the sum is the partition total for every row.
Complete Example Session
-- ============================================
-- PART 1: BASIC WINDOW AGGREGATE
-- ============================================
SELECT
employee_id,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;-- ============================================
-- PART 2: WINDOW WITHOUT PARTITION
-- ============================================
SELECT
employee_id,
salary,
AVG(salary) OVER () AS overall_avg
FROM employees;-- ============================================
-- PART 3: ROW_NUMBER
-- ============================================
SELECT
employee_id,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;-- ============================================
-- PART 4: RANK vs DENSE_RANK
-- ============================================
SELECT
employee_id,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;-- ============================================
-- PART 5: LAG AND LEAD
-- ============================================
SELECT
order_date,
amount,
LAG(amount) OVER (ORDER BY order_date) AS prev_amount,
LEAD(amount) OVER (ORDER BY order_date) AS next_amount
FROM orders;-- ============================================
-- PART 6: RUNNING TOTAL
-- ============================================
SELECT
order_date,
amount,
SUM(amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;-- ============================================
-- PART 7: PARTITION TOTAL
-- ============================================
SELECT
order_date,
amount,
SUM(amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS total
FROM orders;-- ============================================
-- PART 8: NTILE
-- ============================================
SELECT
employee_id,
salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;-- ============================================
-- PART 9: WINDOW CLAUSE
-- ============================================
SELECT
employee_id,
department,
salary,
AVG(salary) OVER w AS dept_avg,
MAX(salary) OVER w AS dept_max
FROM employees
WINDOW w AS (PARTITION BY department);-- ============================================
-- PART 10: TOP N PER GROUP
-- ============================================
SELECT * FROM (
SELECT
product_id,
category,
price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products
) AS ranked
WHERE rn <= 3;The ten parts covered a basic window aggregate, a window without partition, ROW_NUMBER, RANK versus DENSE_RANK, LAG and LEAD, a running total, a partition total, NTILE, the WINDOW clause, and top-N per group.
Quick Reference
Window Function Syntax
| Part | Purpose |
|---|---|
function() | The function to apply |
OVER (...) | The window specification |
PARTITION BY | Divide into groups |
ORDER BY | Order within groups |
| Frame clause | Define which rows |
Aggregate Window Functions
| Function | Result |
|---|---|
SUM(x) OVER (...) | Sum over window |
AVG(x) OVER (...) | Average over window |
MIN(x) OVER (...) | Minimum over window |
MAX(x) OVER (...) | Maximum over window |
COUNT(*) OVER (...) | Count over window |
Ranking Functions
| Function | Ties | Gaps |
|---|---|---|
ROW_NUMBER() | Unique | No |
RANK() | Same rank | Yes |
DENSE_RANK() | Same rank | No |
NTILE(n) | Buckets | N/A |
PERCENT_RANK() | Percentile | N/A |
CUME_DIST() | Cumulative | N/A |
Offset Functions
| Function | Result |
|---|---|
LAG(x, n) | Value n rows before |
LEAD(x, n) | Value n rows after |
FIRST_VALUE(x) | First value |
LAST_VALUE(x) | Last value |
NTH_VALUE(x, n) | nth value |
Frame Clauses
| Frame | Meaning |
|---|---|
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | Running total |
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING | Remaining total |
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING | Entire partition |
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING | Current ± 1 |
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW | Rolling 7 days |
Best Practices
✅ Do This:
-- Use PARTITION BY to group
AVG(salary) OVER (PARTITION BY department) -- ✅
-- Use ORDER BY for ranking and running totals
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) -- ✅
-- Specify the frame for running totals
SUM(amount) OVER (ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- ✅
-- Use the WINDOW clause for repeated specifications
WINDOW w AS (PARTITION BY department) -- ✅
-- Use ROW_NUMBER for top-N per group
SELECT * FROM (SELECT ..., ROW_NUMBER() OVER (...) AS rn) t
WHERE rn <= 3; -- ✅
-- Use LAG/LEAD for row comparisons
amount - LAG(amount) OVER (ORDER BY date) -- ✅❌ Don’t Do This:
-- Don't use a window function in WHERE
WHERE ROW_NUMBER() OVER (...) = 1 -- error -- ❌
-- Don't forget the frame for running totals
SUM(amount) OVER (ORDER BY date) -- running by default, ok -- ✅
-- but for the partition total, you need the frame -- ⚠️
-- Don't use RANK when ROW_NUMBER is needed
RANK() OVER (...) -- ties get the same rank -- ⚠️
-- Don't nest window functions
SUM(AVG(x) OVER (...)) OVER (...) -- error -- ❌
-- Don't use ORDER BY in the frame without understanding the default
-- The default frame is a running aggregate, not the partition -- ⚠️
-- Don't use window functions when a GROUP BY is simpler
AVG(salary) OVER (PARTITION BY dept) -- if you only want the avg -- ⚠️Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
Window function in WHERE | Not allowed | Wrap in a subquery |
| Wrong frame | Default is running | Specify the frame |
RANK vs ROW_NUMBER | Ties behavior | Choose deliberately |
LAST_VALUE returns current | Default frame | Use ROWS BETWEEN ... AND UNBOUNDED FOLLOWING |
Window with no PARTITION | Computes over all rows | Add PARTITION BY |
| Nested window | Not allowed | Use a subquery |
| Performance | No index | Index the partition columns |
Real-World Examples
1. Department Average per Employee
SELECT
employee_id,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;2. Employees Above Department Average
SELECT * FROM (
SELECT
employee_id,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees
) t
WHERE salary > dept_avg;3. Top 3 per Category
SELECT * FROM (
SELECT
product_id,
category,
price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products
) t
WHERE rn <= 3;4. Running Total
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;5. Percentage of Total
SELECT
order_date,
amount,
100.0 * amount / SUM(amount) OVER () AS pct
FROM orders;6. Row Comparison
SELECT
order_date,
amount,
amount - LAG(amount) OVER (ORDER BY order_date) AS change
FROM orders;7. Ranking
SELECT
employee_id,
salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;8. Quartiles
SELECT
employee_id,
salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;9. Rolling 7-Day Total
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) AS rolling
FROM orders;10. First and Last
SELECT
employee_id,
salary,
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS top,
LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bottom
FROM employees;Visual
Window vs Group By
┌─────────────────────────────────────────────────────────────┐
│ GROUP BY │
│ │
│ SELECT department, AVG(salary) │
│ FROM employees GROUP BY department; │
│ │
│ ┌────────────┬──────────┐ │
│ │ department │ avg │ │
│ ├────────────┼──────────┤ │
│ │ Eng │ 90000 │ │
│ │ Sales │ 70000 │ │
│ └────────────┴──────────┘ │
│ │
│ One row per group. Individual rows are gone. │
│ │
├─────────────────────────────────────────────────────────────┤
│ │
│ WINDOW FUNCTION │
│ │
│ SELECT employee_id, department, salary, │
│ AVG(salary) OVER (PARTITION BY department) AS avg │
│ FROM employees; │
│ │
│ ┌─────────────┬────────────┬────────┬───────┐ │
│ │ employee_id │ department │ salary │ avg │ │
│ ├─────────────┼────────────┼────────┼───────┤ │
│ │ 1 │ Eng │ 95000 │ 90000 │ │
│ │ 2 │ Eng │ 85000 │ 90000 │ │
│ │ 3 │ Sales │ 75000 │ 70000 │ │
│ │ 4 │ Sales │ 65000 │ 70000 │ │
│ └─────────────┴────────────┴────────┴───────┘ │
│ │
│ One row per input row. Aggregate attached. │
│ │
└─────────────────────────────────────────────────────────────┘ROW_NUMBER vs RANK vs DENSE_RANK
┌─────────────────────────────────────────────────────────────┐
│ SALARIES: 100, 90, 90, 80 │
│ │
│ ┌──────┬────────────┬──────┬────────────┐ │
│ │ sal │ ROW_NUMBER │ RANK │ DENSE_RANK │ │
│ ├──────┼────────────┼──────┼────────────┤ │
│ │ 100 │ 1 │ 1 │ 1 │ │
│ │ 90 │ 2 │ 2 │ 2 │ │
│ │ 90 │ 3 │ 2 │ 2 │ │
│ │ 80 │ 4 │ 4 │ 3 │ │
│ └──────┴────────────┴──────┴────────────┘ │
│ │
│ ROW_NUMBER: unique, no ties │
│ RANK: ties share, next rank skips │
│ DENSE_RANK: ties share, next rank does not skip │
│ │
└─────────────────────────────────────────────────────────────┘Frame Clause
┌─────────────────────────────────────────────────────────────┐
│ ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW │
│ │
│ ┌──────┬────────┐ │
│ │ row │ sum │ │
│ ├──────┼────────┤ │
│ │ 1 │ 10 │ ← rows 1-1 │
│ │ 2 │ 30 │ ← rows 1-2 │
│ │ 3 │ 60 │ ← rows 1-3 │
│ │ 4 │ 100 │ ← rows 1-4 │
│ └──────┴────────┘ │
│ │
│ Running total. │
│ │
├─────────────────────────────────────────────────────────────┤
│ │
│ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING │
│ │
│ ┌──────┬────────┐ │
│ │ row │ sum │ │
│ ├──────┼────────┤ │
│ │ 1 │ 100 │ ← all rows │
│ │ 2 │ 100 │ ← all rows │
│ │ 3 │ 100 │ ← all rows │
│ │ 4 │ 100 │ ← all rows │
│ └──────┴────────┘ │
│ │
│ Partition total. │
│ │
└─────────────────────────────────────────────────────────────┘Top N per Group
┌─────────────────────────────────────────────────────────────┐
│ INNER QUERY │
│ │
│ SELECT product_id, category, price, │
│ ROW_NUMBER() OVER ( │
│ PARTITION BY category │
│ ORDER BY price DESC) AS rn │
│ FROM products; │
│ │
│ ┌────────┬──────────┬───────┬────┐ │
│ │ id │ category │ price │ rn │ │
│ ├────────┼──────────┼───────┼────┤ │
│ │ 1 │ A │ 100 │ 1 │ │
│ │ 2 │ A │ 90 │ 2 │ │
│ │ 3 │ A │ 80 │ 3 │ │
│ │ 4 │ B │ 200 │ 1 │ │
│ │ 5 │ B │ 150 │ 2 │ │
│ └────────┴──────────┴───────┴────┘ │
│ │
│ OUTER QUERY │
│ WHERE rn <= 3 │
│ │
│ Returns the top 3 products per category. │
│ │
└─────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Syntax | function() OVER (spec) |
PARTITION BY | Divide into groups |
ORDER BY | Order within groups |
| Frame | Define which rows |
ROW_NUMBER() | Unique sequence |
RANK() | Ties share, gaps |
DENSE_RANK() | Ties share, no gaps |
NTILE(n) | Divide into n buckets |
LAG(col, n) | Value n rows before |
LEAD(col, n) | Value n rows after |
FIRST_VALUE | First value in window |
LAST_VALUE | Last value in window |
| Default frame | Running aggregate if ORDER BY |
Key takeaways:
- A window function computes a value across a set of rows without collapsing them. The result has one row per input row, with the computed value attached. This is the difference from
GROUP BY. - The
OVERclause defines the window.PARTITION BYdivides the rows into groups,ORDER BYorders them within each group, and the frame clause defines which rows are included relative to the current row. - Aggregate functions can be used as window functions.
SUM,AVG,MIN,MAX, andCOUNTwork withOVER. The dedicated window functions are the ranking, offset, and distribution functions. ROW_NUMBER,RANK, andDENSE_RANKdiffer in ties.ROW_NUMBERis unique.RANKgives ties the same rank and skips the next.DENSE_RANKgives ties the same rank and does not skip.LAGandLEADaccess other rows.LAGgives the value fromnrows before, andLEADgives the value fromnrows after. They are the tools for row comparisons.- The default frame is a running aggregate. When an
ORDER BYis present, the default frame isRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. To get the entire partition, specifyROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. - Window functions cannot be used in
WHERE. They are evaluated after theWHEREclause. To filter on a window function, wrap the query in a subquery and filter in the outer query.
Remember: Window functions are the SQL tool for computing values across rows without collapsing them. They are the answer to “per group” and “per row” questions that GROUP BY cannot answer. The OVER clause is the mechanism: PARTITION BY divides, ORDER BY orders, and the frame defines the window. Use them for running totals, rankings, top-N per group, row comparisons, and percentages of total. The syntax is unfamiliar at first, but the concepts are the ones that appear in every report: compare each row to its group, its neighbor, and its total. The window function is how.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!