SQL Window Functions: How They Work and When to Use Them

SQL window functions let you perform calculations across a set of rows that are related to the current row, without collapsing those rows into a single result. That's the key difference from GROUP BY. You keep every row. The calculation just looks at a defined "window" of data around it. 

This blog covers everything you need to actually use window functions in SQL: the syntax, the most common types, practical examples, and a few places where beginners get tripped up. By the end, you'll know when to reach for a window function instead of a subquery or a join. 

Explore upGrad's Data Science, AI, and Machine Learning programs to build practical skills in SQL, database management, data analysis, data visualization, statistical modeling, machine learning, and data-driven decision-making for real-world business applications.

What Are Window Functions in SQL? 

A window function performs a calculation over a partition of rows. It doesn't group them or reduce them. Each row gets its own result, and the original row count stays intact. 

Here's the simplest way to think about it. Imagine a sales table with one row per employee per month. If you want each employee's monthly sales alongside their average sales across all months, GROUP BY won't work cleanly. You'd collapse rows. A window function keeps every row and adds the average as a new column next to each one. 

The basic syntax looks like this: 

SELECT 
  employee_id, 
  month, 
  sales, 
  AVG(sales) OVER (PARTITION BY employee_id) AS avg_sales 
FROM sales_data; 

Key Components 

Three parts of the OVER clause: 

Component	Purpose	Example
OVER()	Defines the window over which the calculation is performed. It tells SQL that the function should operate as a window function rather than a regular aggregate function.	SUM(sales) OVER()
PARTITION BY	Splits the data into groups (similar to GROUP BY), but rows are not collapsed. Calculations are performed separately within each partition while retaining all rows.	AVG(salary) OVER(PARTITION BY department)
ORDER BY	Determines the row order within each partition. Required for ranking functions, running totals, and row comparisons.	ROW_NUMBER() OVER(ORDER BY sales DESC)
Frame Clause	Limits which rows within the partition are included in the calculation. Commonly used with ROWS BETWEEN or RANGE BETWEEN for running totals and moving averages.	SUM(sales) OVER(ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
Window Function	Performs the calculation on the defined window. Examples include ranking, aggregation, and row comparison functions.	ROW_NUMBER(), RANK(), SUM(), AVG(), LAG(), LEAD()

You don't need all three every time. Some functions work fine with just PARTITION BY. Others need ORDER BY to make sense. 

Do read: Master ER Diagram in DBMS: A Guide to Database Design & Management  

 

Types of Window Functions in SQL 

There are three main categories. Each solves a different kind of problem. 

Ranking Functions 

These assign a number to each row within a partition based on its position. 

Function	What It Does
ROW_NUMBER()	Assigns a unique sequential number, no ties
RANK()	Assigns the same rank to ties, skips next ranks
DENSE_RANK()	Assigns the same rank to ties, doesn't skip
NTILE(n)	Divides rows into n equal buckets

When would you use this? Say you want the top 3 customers by purchase value in each city. ROW_NUMBER or RANK inside a CTE, followed by a WHERE on the rank, is the cleanest way to do it. 

SELECT * 
FROM ( 
  SELECT 
    customer_id, 
    city, 
    total_purchase, 
    RANK() OVER (PARTITION BY city ORDER BY total_purchase DESC) AS city_rank 
  FROM customers 
) ranked 
WHERE city_rank <= 3; 

Do read: Fact Table vs Dimension Tables: Modern Data Modeling Guide in 2025 

Aggregate Window Functions 

These are the standard aggregate functions (SUM, AVG, COUNT, MIN, MAX) but used with OVER. You get aggregate results without losing row-level detail. 

SELECT 
  order_id, 
  department, 
  amount, 
  SUM(amount) OVER (PARTITION BY department) AS dept_total, 
  ROUND(amount * 100.0 / SUM(amount) OVER (PARTITION BY department), 2) AS pct_of_dept 
FROM orders; 

That second column, the percentage, would take a self-join or a subquery without window functions. Here it's one line. 

Value Functions 

These look at other rows relative to the current one. 

• LAG(col, n): returns the value from n rows before the current row 
• LEAD(col, n): returns the value from n rows after 
• FIRST_VALUE(col): first value in the window frame 
• LAST_VALUE(col): last value in the window frame 

LAG is especially useful for month-over-month comparisons. You don't need to join the table to itself. 

SELECT 
  month, 
  revenue, 
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue, 
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS change 
FROM monthly_revenue; 

 Also read: Top 10 Real-Time SQL Project Ideas: For Beginners & Advanced 

SQL Window Functions Syntax: The Full Picture 

The syntax trips up a lot of people at first. Here's a clean breakdown. 

function_name(expression) 
  OVER ( 
    [PARTITION BY col1, col2, ...] 
    [ORDER BY col3 [ASC|DESC]] 
    [ROWS | RANGE BETWEEN frame_start AND frame_end] 
  ) 

 The frame clause is the most important part. Without it, the default window frame depends on whether you have ORDER BY or not: 

• No ORDER BY: the frame is the entire partition 
• With ORDER BY: the frame defaults to all rows from the partition start up to the current row 

If you write SUM(sales) OVER (PARTITION BY dept ORDER BY month), you get a running total, not a total for the whole department. That's because the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. 

Want the full department total regardless of ORDER BY? Be explicit: 

SUM(sales) OVER ( 
  PARTITION BY dept 
  ORDER BY month 
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 
) 

A small syntax difference creates a very different result. 

Do read: SQL for Data Science: Functions, Queries, and Best Practices 

Practical Examples of Window Functions in SQL 

Let's look at real scenarios where window functions in SQL save you from writing messy subqueries. 

Running Total 

SELECT 
  order_date, 
  amount, 
  SUM(amount) OVER (ORDER BY order_date) AS running_total 
FROM orders; 

Also read:  A Brief Guide to Working With ‘ALTER’ Command in SQL-Know the Ins and Outs! | upGrad blog 

Percentile Rank 

SELECT 
  student_id, 
  score, 
  PERCENT_RANK() OVER (ORDER BY score) AS percentile 
FROM exam_results; 

Finding Duplicates 

SELECT * 
FROM ( 
  SELECT 
    *, 
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn 
  FROM users 
) t 
WHERE rn > 1; 

Finding duplicate records with ROW_NUMBER is faster and easier to read than a GROUP BY with HAVING COUNT > 1, especially when you want to see the full duplicate rows. 

Comparing Each Row to a Group Average 

SELECT 
  employee_id, 
  department, 
  salary, 
  AVG(salary) OVER (PARTITION BY department) AS dept_avg, 
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg 
FROM employees; 

This kind of output doesn't need any joins or subqueries. It's one SELECT. 

Also read: DBMS Tutorial For Beginners: Everything You Need To Know | upGrad blog 

Common Mistakes When Using Window Functions in SQL 

Even after you understand the concept, a few things catch people off guard  

Using WHERE to filter on a window function result 

You can't do this: 

-- This will error 
SELECT *, RANK() OVER (ORDER BY salary) AS rnk 
FROM employees 
WHERE rnk <= 5; 
The WHERE clause runs before window functions are evaluated. Wrap it in a subquery or CTE first. 
SELECT * FROM ( 
  SELECT *, RANK() OVER (ORDER BY salary) AS rnk 
  FROM employees 
) t 
WHERE rnk <= 5; 

 Do read: Master SQL Update: A Guide to Database Record Modifications 

Forgetting ORDER BY in ranking functions 

ROW_NUMBER without ORDER BY inside OVER gives you arbitrary, non-deterministic numbering. You'll get a result. It just won't mean anything. 

LAST_VALUE with the wrong frame 

If you use LAST_VALUE without explicitly setting the frame to UNBOUNDED FOLLOWING, you'll get the current row's value every time. That's the frame default at work again. 

Performance on large tables 

Window functions are powerful but not always fast. On large datasets without proper indexing, a query with multiple OVER clauses can get heavy. If you're running these in production on millions of rows, check your execution plan. Partitioning columns that match your PARTITION BY clause can help a lot. 

Must read: SQL vs Python: A Detailed Comparison 

When to Use Window Functions vs. GROUP BY 

This question comes up a lot. 

Scenario	Use GROUP BY	Use Window Functions
You need one row per group	Yes	No
You need row-level detail with group stats	No	Yes
Running totals or cumulative sums	No	Yes
Ranking within a group	No	Yes
Comparing rows to previous/next rows	No	Yes
Simple aggregation report	Yes	Overkill

The rule of thumb: if losing rows is okay, GROUP BY is fine. If you need every original row in the output, reach for a window function. 

Benefits of Using SQL Window Functions 

Window functions aren't always the fastest solution. On very large datasets, poor indexing or excessive partitioning can slow execution. Understanding query performance remains important. 

Benefit	Impact
Fewer subqueries	Cleaner code
Better readability	Easier maintenance
Faster analysis	Less query complexity
Advanced reporting	Richer insights
Trend analysis	Better decision-making

Conclusion 

SQL window functions are one of those features that feel complicated the first time, then indispensable after you've used them a few times. The OVER clause is the key. Once you understand what PARTITION BY, ORDER BY, and the frame clause each do, the rest is just knowing which function to pick for the job. 

Start with SUM and ROW_NUMBER using OVER. Run them on a small table. Watch what the output looks like. That hands-on step does more than any explanation. 

Ready to start your journey? Book a free consultation with upGrad today to find the best path for your career.