Window Functions Introduction

Most SQL beginners learn aggregate functions like SUM(), AVG(), and COUNT() together with GROUP BY. That combination is powerful, but it has one big limitation: it collapses many rows into one. If you want a per-row detail alongside a group-level summary — say, "show me every sale, plus the total for that salesperson" — plain aggregation can't do it in a single pass. That is exactly the problem window functions solve.

Note

A window function computes a value across a set of related rows (called the window) but does not reduce those rows into a single output row. Every original row survives, now carrying the computed value alongside it.

Our Sample Dataset

Throughout this window functions series we will reuse the same small sales table so you can compare techniques directly. It records daily revenue per salesperson:

SQL

CREATE TABLE sales (
  id            INT PRIMARY KEY,
  sale_date     DATE,
  salesperson   VARCHAR(50),
  region        VARCHAR(20),
  amount        DECIMAL(10, 2)
);

INSERT INTO sales (id, sale_date, salesperson, region, amount) VALUES
  (1, '2024-01-01', 'Amir',  'East', 400.00),
  (2, '2024-01-02', 'Amir',  'East', 250.00),
  (3, '2024-01-03', 'Amir',  'East', 600.00),
  (4, '2024-01-01', 'Bilal', 'West', 300.00),
  (5, '2024-01-02', 'Bilal', 'West', 300.00),
  (6, '2024-01-03', 'Bilal', 'West', 150.00),
  (7, '2024-01-01', 'Chen',  'East', 500.00);

GROUP BY Collapses Rows — Window Functions Don't

Suppose we want the total revenue earned by each salesperson. A regular aggregate query with GROUP BY does this, but the individual sales are gone from the result — we only get one row per salesperson.

SQL

-- GROUP BY: collapses every salesperson's rows into ONE summary row
SELECT
  salesperson,
  SUM(amount) AS total_sales
FROM sales
GROUP BY salesperson;


salesperson | total_sales
------------|------------
Amir        | 1250.00
Bilal       | 750.00
Chen        | 500.00

Now compare that to the same total computed with a window function. Notice that all seven original rows are still present — we simply added a new column showing each person's total.

SQL

-- Window function: every row survives, total is added alongside it
SELECT
  id,
  sale_date,
  salesperson,
  amount,
  SUM(amount) OVER (PARTITION BY salesperson) AS person_total
FROM sales
ORDER BY salesperson, sale_date;


id | sale_date  | salesperson | amount | person_total
---|------------|-------------|--------|-------------
1  | 2024-01-01 | Amir        | 400.00 | 1250.00
2  | 2024-01-02 | Amir        | 250.00 | 1250.00
3  | 2024-01-03 | Amir        | 600.00 | 1250.00
4  | 2024-01-01 | Bilal       | 300.00 | 750.00
5  | 2024-01-02 | Bilal       | 300.00 | 750.00
6  | 2024-01-03 | Bilal       | 150.00 | 750.00
7  | 2024-01-01 | Chen        | 500.00 | 500.00

Tip

This is the single most important idea to internalize about window functions: they add a computed column without removing detail rows. That's why they're sometimes described as "GROUP BY that doesn't group."

The General Syntax

Every window function follows the same shape: a function call immediately followed by an OVER clause that defines the window.

SQL

function_name(expression) OVER (
  [PARTITION BY column1, column2, ...]
  [ORDER BY column3, ...]
  [ROWS or RANGE frame_clause]
)

function_name(expression) — an aggregate function like SUM, AVG, COUNT, or a dedicated window function like ROW_NUMBER, RANK, LAG, LEAD.
PARTITION BY — optional; splits rows into independent groups the function operates within (covered in depth in the next lesson).
ORDER BY — optional; defines the order rows are processed in, required for order-sensitive functions like ranking or running totals.
ROWS/RANGE — optional; fine-tunes exactly which rows within the partition are included (covered later in Window Frame Clauses).

Why Window Functions Matter

Before window functions existed in SQL, analysts had to reach for awkward, often slow workarounds to answer completely ordinary business questions:

Question	Without window functions	With window functions
What % of the region's total does this sale represent?	Self-join the table to a per-region aggregate subquery	`amount / SUM(amount) OVER (PARTITION BY region)`
What is the running total of sales over time?	A correlated subquery re-summing all prior rows for every row	`SUM(amount) OVER (ORDER BY sale_date)`
Who is the top salesperson in each region?	GROUP BY plus a second query to find the max, then join back	`RANK() OVER (PARTITION BY region ORDER BY amount DESC)`
How does today's sale compare to yesterday's?	A self-join on sale_date - 1	`LAG(amount) OVER (ORDER BY sale_date)`

The self-join and correlated-subquery approaches are not just harder to write — they are usually much slower, because the database has to repeatedly scan or join the same rows. Window functions let the engine compute these results in a single, well-optimized pass over the data.

Warning

Window functions are evaluated after WHERE, GROUP BY, and HAVING, but before ORDER BY and LIMIT in the logical order of query processing. This means you cannot reference a window function's result directly in the same query's WHERE clause — you would need to wrap the query in a subquery or CTE first.

What's Next

The rest of this series walks through the window function toolkit one piece at a time: PARTITION BY and ORDER BY inside OVER(), ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE), value-offset functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE), and finally running totals, moving averages, and explicit frame clauses. Each lesson builds on the same sales sample data shown above.