Article
•
Mastering Window Functions in SQL – How to Think & Solve Questions Step by Step (From Noob to Pro)
Mastering Window Functions in SQL – How to Think & Solve Questions Step by Step (From Noob to Pro)
Hey, I'm Gaurav.
When I first saw window functions in SQL interviews, I was completely lost.
The interviewer asked:
"Find the running total of sales for each customer."
My brute-force brain immediately went into panic mode:
Noob Way (What most beginners do):
- Try to use GROUP BY → but then I lose individual rows
- Try to write nested subqueries → query becomes unreadable
- Eventually give up and say "I’ll do it in Python"
Sound familiar?
Today, I’m going to show you exactly how I learned to think about window functions — from that confused state to confidently solving them in interviews.
We’ll go step by step using real examples from our `orders` and `users` tables. You can practice every question live here: [sqlpractice.in](https://sqlpractice.in)
Let’s begin.
First, Understand the Problem with Brute Force Thinking
Most people (including me initially) think like this:
"I need to calculate something for each row, but also use data from other rows."
So we try:
- Subqueries (very slow and messy)
- Self-joins (complicated)
- Temporary tables (overkill)
The real breakthrough comes when you realize:
> Window functions let you perform calculations across a set of rows without collapsing them like GROUP BY does.
What Are Window Functions? (Simple Explanation)
Normal aggregate functions (`SUM`, `AVG`, `COUNT`) + `GROUP BY` → reduce many rows into one row.
Window functions do the same calculation but keep all rows in the result.
They use the `OVER()` clause.
Basic syntax:
```sql
SUM(column) OVER (PARTITION BY group_column ORDER BY sort_column)
```
- `PARTITION BY` → like GROUP BY, but doesn’t collapse rows
- `ORDER BY` → defines the order for running calculations
- `ROWS BETWEEN` → defines the window frame (optional)
Example 1: Running Total (Cumulative Sum)
Question: Show each order with its amount and the running total of sales up to that point for the same customer.
Noob approach:
- Use a subquery for every row → very slow
- Or try to do it in application code
Better thinking process:
1. I need the sum of amounts for each customer.
2. But I want the sum to accumulate row by row, ordered by date.
3. So I need `SUM()` with `OVER()` + `PARTITION BY customer` + `ORDER BY date`.
Correct Solution:
```sql
SELECT
o.id,
u.name AS customer,
o.total_price,
SUM(o.total_price) OVER (
PARTITION BY u.id
ORDER BY o.ordered_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders o
JOIN users u ON o.user_id = u.id
ORDER BY u.id, o.ordered_at;
```
Practice Question 1 (Easy)
Show each order with its amount and running total per customer.
[Try it live →](https://sqlpractice.in/?question=window-running-total)
Example 2: Ranking (RANK vs DENSE_RANK vs ROW_NUMBER)
Question: Rank employees by salary within each department.
Noob thinking:
- Sort the data and manually assign ranks in code
Better thinking:
1. I need to assign ranks.
2. Ranking should reset for every department → `PARTITION BY department`
3. I need to decide how to handle ties → RANK, DENSE_RANK, or ROW_NUMBER
Correct Solution:
```sql
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_with_gaps,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_number
FROM employees;
```
Practice Question 2 (Medium)
Rank customers by their total spending (highest first).
[Try it live →](https://sqlpractice.in/?question=window-ranking)
Example 3: Previous & Next Value (LAG and LEAD)
Question: Show each order amount and the previous order amount for the same customer.
Noob approach: Complicated self-join with dates
Better thinking:
- I need the value from the previous row in the same partition.
- Use `LAG()` → looks backward.
Correct Solution:
```sql
SELECT
o.id,
u.name,
o.total_price,
LAG(o.total_price) OVER (PARTITION BY u.id ORDER BY o.ordered_at) AS previous_order_amount
FROM orders o
JOIN users u ON o.user_id = u.id;
```
Practice Question 3 (Hard)
Show month-over-month growth in total sales.
[Try it live →](https://sqlpractice.in/?question=window-lag-lead)
How to Approach Any Window Function Question (My Thinking Framework)
When you see a window function question, follow this mental checklist:
1. What do I need to calculate? (sum, rank, previous value, etc.)
2. Should it reset per group? → Use `PARTITION BY`
3. In what order? → Use `ORDER BY`
4. Over which rows? → Use `ROWS BETWEEN` or default frame
5. Which function?
- Running total → `SUM() OVER`
- Ranking → `RANK()`, `DENSE_RANK()`, `ROW_NUMBER()`
- Previous/Next → `LAG()` / `LEAD()`
- Divide into groups → `NTILE()`
Final Thoughts
I used to fear window functions.
Now they are one of my strongest areas because I learned to think about them instead of memorizing syntax.
The key is:
Don’t try to write the perfect query in one go.
Break it down: What do I need to calculate? How should it be grouped? In what order?
Go practice these questions on [sqlpractice.in](https://sqlpractice.in) — the live editor will show you immediately whether your thinking is correct.
Next article: Types of Views in SQL
Keep practicing — you’re getting better every day! 💪
Building sqlpractice.in to help everyone master SQL for interviews — completely free.
---
Would you like me to write the next article ("Types of Views in SQL") in the same style?
Or do you want any changes in this Window Functions article before publishing?
Let me know! 🚀