SQL Window Functions Introduction

Window functions are the feature that makes analysts reach for SQL instead of a spreadsheet. They let you rank rows, number them, and compute running totals — all while keeping every original row visible. Once they click, a whole class of "top N per group" and "running total" problems that felt impossible become two lines of SQL.

They build on aggregation, so make sure GROUP BY in SQL is solid first. The key contrast to hold in mind: GROUP BY collapses rows into summaries, while a window function keeps every row and adds a computed column beside it.

Our sample data

-- employees
emp_id | emp_name | department_id | salary
-------+----------+---------------+--------
101    | Aarti    | 1             | 78000
102    | Bhaskar  | 1             | 65000
103    | Chitra   | 2             | 52000
104    | Devan    | 2             | 58000
105    | Esha     | 3             | 47000
106    | Farhan   | 4             | 90000

The OVER clause: what makes a function a window function

Any aggregate becomes a window function when you add an OVER clause. Compare these two queries. The first collapses; the second does not.

-- GROUP BY: one row, loses detail
SELECT AVG(salary) AS company_avg
FROM employees;

-- Window: every row kept, average shown beside each
SELECT emp_name,
       salary,
       AVG(salary) OVER () AS company_avg
FROM employees;

The second query returns all six employees, each with an extra column holding the company average. The empty OVER () means "the window is the entire result set." This is already useful — now every row can be compared to the overall average without a subquery.

PARTITION BY: windows within groups

An empty window spans all rows. PARTITION BY splits the rows into groups and restarts the calculation within each one — like GROUP BY, but without collapsing. To show each employee's salary next to their department's average:

SELECT emp_name,
       department_id,
       salary,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees;

Now Aarti and Bhaskar (both department 1) share a dept_avg of 71500, while Chitra and Devan (department 2) share 55000. Every row survives, and each carries its own department's average. Achieving this with GROUP BY alone is impossible because grouping would destroy the individual rows.

Pro tip: Read OVER (PARTITION BY x) as "for each group of x, compute this." It is the mental bridge from GROUP BY to window functions — same grouping idea, but the detail rows stay.

Ranking functions: ROW_NUMBER, RANK, DENSE_RANK

The most-used window functions assign a position to each row. Add ORDER BY inside OVER to define what "position" means:

SELECT emp_name,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

The three differ only in how they treat ties. Suppose two employees earned the same salary:

Knowing which to use is a frequent interview checkpoint. ROW_NUMBER for "pick exactly one per group," RANK when gaps after ties are acceptable, DENSE_RANK when they are not.

Interview note: "Difference between RANK and DENSE_RANK?" is asked constantly. The one-line answer: RANK leaves gaps after ties (1,1,3), DENSE_RANK does not (1,1,2). Add that ROW_NUMBER never ties, and you have covered the whole family.

Top-N-per-group: the killer use case

Here is the problem window functions were made for: "the highest-paid employee in each department." With PARTITION BY plus ROW_NUMBER, it is direct:

SELECT emp_name, department_id, salary
FROM (
    SELECT emp_name,
           department_id,
           salary,
           ROW_NUMBER() OVER (
               PARTITION BY department_id
               ORDER BY salary DESC
           ) AS rn
    FROM employees
) ranked
WHERE rn = 1;

Inside each department the rows are ranked by salary, then the outer query keeps only rank 1. Doing this with a correlated subquery is possible but clumsier, which is why window functions are the modern default for ranking problems.

Running totals with aggregate windows

Add ORDER BY to an aggregate window and it becomes a running calculation — accumulating as it goes. A running total of salary, ordered by employee id:

SELECT emp_name,
       salary,
       SUM(salary) OVER (ORDER BY emp_id) AS running_total
FROM employees;

Each row's running_total is the sum of all salaries up to and including that row. Combine with PARTITION BY to reset the running total per department. Running totals, moving averages and cumulative counts all follow this pattern, and they are staples of financial and analytics reporting.

LAG and LEAD: peeking at neighbor rows

LAG and LEAD let a row see the value from a previous or next row — perfect for period-over-period comparisons:

SELECT emp_name,
       salary,
       LAG(salary) OVER (ORDER BY salary) AS prev_salary,
       salary - LAG(salary) OVER (ORDER BY salary) AS gap
FROM employees;

Ordered by salary, each row shows the salary just below it and the gap. In real data this pattern computes month-over-month growth, day-over-day change, and similar deltas without a self-join.

NTILE: splitting rows into buckets

NTILE(n) divides the ordered rows into n roughly equal buckets and labels each row with its bucket number. It is how you build quartiles, deciles and percentile bands:

SELECT emp_name,
       salary,
       NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

The highest earners land in quartile 1, the lowest in quartile 4. Analysts use NTILE constantly to segment customers by spend or employees by performance without hard-coding thresholds. Because it partitions by rank position rather than value, the buckets stay balanced even as the data changes.

Named windows and reuse

When several window functions share the same OVER definition, repeating it is noisy and error-prone. The WINDOW clause lets you name a window once and reuse it:

SELECT emp_name,
       salary,
       RANK()       OVER w AS rnk,
       DENSE_RANK() OVER w AS dense_rnk,
       ROW_NUMBER() OVER w AS row_num
FROM employees
WINDOW w AS (ORDER BY salary DESC);

All three functions reference the named window w, keeping the query clean and guaranteeing they use identical ordering. Both PostgreSQL and MySQL 8.0+ support named windows. It is a small feature that pays off as your analytical queries grow.

Common mistakes

Confusing window ORDER BY with the query's ORDER BY. The ORDER BY inside OVER (...) controls the window calculation, not the final row order. You often need a separate ORDER BY at the end of the query to sort the output.

Trying to filter on a window column in WHERE. Window functions are computed after WHERE, so you cannot reference rn in a WHERE clause of the same query level. That is exactly why the top-N example wraps the window query in a subquery and filters in the outer query.

Common mistake: Writing WHERE ROW_NUMBER() OVER (...) = 1 and getting an error. Window functions run late in the execution order, after WHERE. Compute the window in a subquery or CTE, then filter its result in the outer query.

Window functions versus GROUP BY

The final distinction, stated plainly:

• Use GROUP BY when you want a summary and do not need the individual rows — total sales per region, headcount per department.
• Use a window function when you need the summary and the detail together — each sale next to its region's total, each employee next to their department's rank.

Mastering when to pick each is a sign of real SQL fluency, and it is a skill the data analyst roadmap leans on heavily.

Where to go next

Window functions round out the core of analytical SQL. With OVER, PARTITION BY and the ranking functions, you can solve ranking, running-total and neighbor-comparison problems that stump beginners.

Revisit subqueries in SQL to see how the older approaches compare, then apply everything on the classic second-highest-salary problem, which has an elegant window-function solution. From here, the whole SQL learning hub opens up toward indexes, transactions and design.