SQL Tutorial
•
•
4 min read
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.