At first glance, it might seem like SQL already has everything you need for data analysis: GROUP BY, aggregates, subqueries... But that's just the beginning. Welcome to the world of window functions — a powerful tool that lets you work with data row by row, keeping all the context intact.
Window functions let you do calculations — sum, average, ranks, and more — over a "window" of rows without collapsing your data. That means, unlike regular aggregate functions (SUM(), AVG(), COUNT()), you get both the result and the details in every row.
Imagine you want to calculate cumulative revenue by orders. With GROUP BY you'd lose the individual orders — you'd just get the total. But with a window function, you add the result right to each row, losing nothing.
Window functions are especially handy because they don't destroy your data: every row stays in place, and the calculation results just get added as new columns. This lets you do complex analytics without subqueries or clunky constructions — everything happens right inside a single query. These functions are perfect for stuff like ranking, calculating moving averages, or comparing values between rows. Your code stays readable, and your results — spot on.
When is this especially useful:
- Ranking employees, sellers, products — see who's in what place.
- Time series — how something changed by day or week.
- Sales and finance — how much has accumulated at each step, which orders are above average, who's in the top 25%.
Where are window functions used
In any area where context matters, not just the final result:
- sales reports;
- customer behavior analysis;
- building charts with cumulative metrics;
- data segmentation (like by quartiles);
- calculating deviations and trends.
They're a real find for analysts working with SQL every day. Let's check out some real-world examples where window functions can totally save your day.
Example 1: Ranking data
Imagine you have a list of students with their exam grades. You want to assign each student their position in the class. With window functions, this is a breeze. For example, the RANK() or ROW_NUMBER() functions will help you nail this task.
Example 2: Analyzing time-based data
What if you need to see how a company's revenue changed month by month? You need a cumulative sum of revenue. Using the SUM() window function with a specific window, you can easily get this result.
Example 3: Quantiles and grouping
Want to split your data into equal groups (like by income) for customer segmentation? The NTILE() function has your back. Let's find out who's in the top 25% of clients and who's at the bottom of the ranking.
What does it look like?
A window function just adds the result to your final dataset:
SELECT
student_id,
grade,
RANK() OVER (ORDER BY grade DESC) AS rank
FROM
students;
Here we get a table where each student has their unique rank by grade.
A simple analogy
Imagine you're out running with a group of friends. Everyone's running at their own pace, but you want to know what place you're in right now. Instead of stopping everyone and making a leaderboard (like GROUP BY does), you just look around at who's running near you and figure out your current ranking.
That's what a window function does: it doesn't stop the race, doesn't split everyone into groups — it just adds info, keeping the movement and details. Everyone keeps running, but now you have extra analytics — like how many people are ahead, how your pace compares to the average, and so on.
Advantages over traditional approaches
Let's look at a classic task: ranking salespeople by revenue. There are two approaches:
Without window functions. You'd need to make a subquery or even several subqueries to first sort the data, then number it. It's not just long, it's also hard to read.
With window functions. Just one query with clean and clear syntax, and you've got your result. For example:
SELECT
seller_id,
revenue,
RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank_in_region
FROM
sales;
This query instantly splits sellers by region and numbers them in descending order of revenue.
Real-world example
Now imagine you're an analyst looking at sales data. You need to find out:
- total revenue for each month,
- how revenue changed compared to the previous month,
- rank regions by total revenue.
You can do all this using window functions, even in a single query. But that's a topic for the next lectures.
Now, armed with the basics of window functions, you're ready to dive into their syntax and see the power of ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE(). On to the next lecture!
GO TO FULL VERSION