You can filter, group and summarize data and still fail a SQL interview. The questions that trip people up ask for the second-highest salary per department, a running total, or how this month compares to last. A plain `GROUP BY` cannot answer these, because it throws away the rows you need.
SQL window functions are the fix. They run a calculation across related rows while keeping every row in the output. This guide teaches them by the problem each one solves, with worked examples you can run.
Quick answer: What are SQL window functions?
They perform a calculation across a set of related rows while keeping each row in the result. Unlike `GROUP BY`, which collapses rows into one summary line, a window function adds the calculated value next to every original row. They power rankings, running totals and row-to-row comparisons.
What Are SQL Window Functions?
A window function runs a calculation across a set of related rows and returns a value for every row. Nothing is collapsed.
Here it places each department’s average salary next to every employee:
SELECT employee_id, department, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
Three parts do the work:
- `AVG(salary)` is the calculation.
- `OVER()` turns it into a window function.
- `PARTITION BY department` is how to use partition by in SQL: it sets the group the calculation runs over, without dropping rows.
These are also called SQL analytical functions, so a tutorial titled SQL analytical functions with examples covers the same ground.
How Are They Different From GROUP BY?
`GROUP BY` collapses rows. Ask for the average salary per department and you get one line per department. The employees disappear.
A window function keeps the rows. The PostgreSQL documentation puts it precisely: the rows “retain their separate identities.”
So the average sits next to every employee, in the same result. That is what lets you compare each person against it.
| GROUP BY | Window function | |
|---|---|---|
| Rows in output | One per group | Every original row |
| Best for | Summary reports | Rankings, running totals, comparisons |
| Loses detail | Yes | No |
What Is the Syntax of the OVER() Clause?
Everything inside `OVER()` follows a fixed order. Microsoft’s T-SQL reference lists it as `PARTITION BY`, then `ORDER BY`, then the frame clause. Reverse them and the query fails.
— Correct
SUM(salary) OVER (
PARTITION BY department
ORDER BY salary DESC
)
Three mistakes cause most errors when people start SQL window function practice:
- No `ORDER BY` in a ranking function. `ROW_NUMBER()`, `RANK()` and `DENSE_RANK()` need an order to rank by, or the result is meaningless.
- Treating `PARTITION BY` as de-duplication. It groups rows for the calculation, it does not remove duplicates.
- Ignoring the default frame. With `ORDER BY` present, the database applies its own frame unless you set one. More on that below.
SQL Window Functions With Examples: The Three Types
Window functions split into three jobs. Learn each by the question it answers. The SQL window functions examples below use a small `employees` and `orders` table, and the outputs shown are real query results.
How Do Aggregate Window Functions Work?
These are `SUM()`, `AVG()`, `COUNT()`, `MIN()` and `MAX()`, run as windows. A company wants each employee’s salary shown next to the department total, not instead of it.
SELECT employee_id, department, salary,
SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;
Every row keeps the employee’s salary and gains the department total. Swap `SUM` for `AVG` to answer “who earns more than their department average,” which `GROUP BY` cannot do in one query.
How Do Ranking Functions Work in SQL?
Ranking in SQL is where interviews concentrate. Four functions order rows within each partition:
- `ROW_NUMBER()`: a unique number per row, no ties.
- `RANK()`: ties share a rank, then it skips the next numbers.
- `DENSE_RANK()`: ties share a rank, no skipping.
- `NTILE(n)`: splits rows into n equal buckets.
The gap between `RANK()` and `DENSE_RANK()` is the detail interviewers probe:
SELECT employee_id, salary,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;
With two salaries of ₹60,000, a ₹50,000 and a ₹45,000, the output is:
| Salary | RANK() | DENSE_RANK() |
|---|---|---|
| 60,000 | 1 | 1 |
| 60,000 | 1 | 1 |
| 50,000 | 3 | 2 |
| 45,000 | 4 | 3 |
`RANK()` jumps from 1 to 3 because two rows filled position two. `DENSE_RANK()` never leaves a gap. That one difference decides second-highest-salary and top-N questions.
How Do LAG and LEAD Compare Rows?
Navigation functions read a value from another row, so you compare this month to last without a self-join.
- `LAG()`: a value from an earlier row.
- `LEAD()`: a value from a later row.
- `FIRST_VALUE()` and `NTH_VALUE()`: a value at a fixed position in the frame.
SELECT order_id, customer_id, amount,
LAG(amount) OVER (PARTITION BY customer_id
ORDER BY order_date) AS prev_amount
FROM orders;
For a customer with orders of ₹1,200, ₹900 then ₹1,500, `prev_amount` reads NULL, 1200, 900.
The first order is NULL because nothing comes before it. `PARTITION BY customer_id` keeps one customer’s history out of another’s. This is the standard pattern for month-over-month questions.
What Are Window Frames?
A frame controls which rows inside the partition a function sees. It is what a running total depends on, and the move into advanced SQL window functions.
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS running_total
FROM orders;
`UNBOUNDED PRECEDING` starts at the first row. `CURRENT ROW` ends at the current one. So ₹1,200, ₹900 and ₹1,500 give a running total of 1200, 2100, 3600.
Narrow the frame to `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` for a moving average instead.
One SQL server window function detail catches people out. With `ORDER BY` and no explicit frame, Microsoft’s T-SQL reference sets the default to `RANGE`, not `ROWS`.
`RANGE` groups rows that share the same `ORDER BY` value, which can quietly change a `SUM()` or `LAST_VALUE()` result. Set the frame yourself when you need exact control.
How Do You Practice Window Functions?
You do not need to memories every function. Learn the fundamentals, then drill the rest on realistic data. This order works as a self-taught SQL window functions tutorial:
1. Lock the basics. `SELECT`, `WHERE`, `ORDER BY`, `GROUP BY`, joins and aggregates. Our top SQL queries for data analysts guide covers these.
2. Learn how to use window functions in SQL. `OVER()`, `PARTITION BY`, `ORDER BY` and frames, one at a time.
3. Solve real tasks. Rank employees by salary, find the second-highest salary, build a running total, compare months with `LAG()`.
For SQL window functions practice online, HackerRank and similar judges run your queries against test data and check the output. Steady practice beats re-reading. For the wider path, see the data analytics learning roadmap.
Three SQL certifications test SQL at different levels: Oracle Database SQL Certified Associate, the Microsoft Certified: Azure Database Administrator Associate, and HackerRank’s SQL skills certification. A certificate signals effort, but a solved set of SQL program questions proves more, so treat certifications as a complement to hands-on work, not a replacement.
If you want structured practice with doubt support, the Certificate in Data Analytics with AI Foundation at Win in Life Academy covers SQL, Excel, Python, Power BI and Tableau, with IBM-integrated modules, a capstone and 1:1 placement mentorship.
Conclusion
Window functions do what `GROUP BY` cannot: calculate across rows without discarding them. Master `OVER()` and `PARTITION BY`, know why `RANK()` skips where `DENSE_RANK()` does not, and most analytics questions become a few clean lines of SQL. Pick one task this week, a running total or a second-highest salary, and write it from scratch. For guided practice, the Certificate in Data Analytics with AI Foundation at Win in Life Academy is one route.
Frequently Asked Questions About Window Functions in SQL
1. What is the difference between a window function and GROUP BY?
GROUP BY` collapses rows into one summary line per group. A window function keeps every row and adds the calculated value beside it, which is why it handles rankings, running totals and row-to-row comparisons that `GROUP BY` cannot.
2. Do all window functions need ORDER BY?
Ranking and navigation functions do, because they depend on row order. Aggregate window functions such as `SUM()` over a partition work without it, though adding `ORDER BY` turns them into running calculations and brings in a default frame.
3. What is the difference between RANK and DENSE_RANK?
Both give tied rows the same rank. `RANK()` then skips the next numbers, so two first-place ties are followed by rank 3. `DENSE_RANK()` skips nothing, so the next rank is 2. The distinction matters for second-highest-salary questions.
4. Where can I find SQL window functions practice questions?
Online judges such as HackerRank and LeetCode offer practice sets with test data. Focus on `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `LAG()` and running totals, the patterns that appear most in analyst interviews.
