SQL Tutorial
•
•
4 min read
Complete Guide to Window Functions in SQL – NTILE, SUM, AVG, COUNT, MIN, MAX with OVER() (From Noob to Pro)
Complete Guide to Window Functions in SQL – NTILE, SUM, AVG, COUNT, MIN, MAX with OVER() (From Noob to Pro)
Hey, I’m Gaurav.
After learning `RANK()`, `DENSE_RANK()`, `ROW_NUMBER()`, `LAG()`, and `LEAD()`, I thought I had mastered window functions.
Then the interviewer asked:
"Divide customers into 4 equal groups based on their total spending."
My noob brain froze again.
I had no idea how to divide rows into buckets.
Today, I’m covering the remaining important window functions that complete the picture:
- `NTILE()` – Divide into equal groups
- Aggregate functions with `OVER()` (`SUM`, `AVG`, `COUNT`, `MIN`, `MAX`)
We’ll go from brute-force confusion to clear thinking, with live practice links.
All examples use our `orders` and `users` tables. Practice live here: [sqlpractice.in](https://sqlpractice.in)
---
1. NTILE() – Dividing Rows into Buckets
Question: Divide all customers into 4 equal groups (quartiles) based on their total spending.
Noob approach:
- Try to use `RANK()` and then manually divide (very messy)
- Write complex subqueries with `COUNT()` and math
Better thinking process:
1. I need to assign each customer to a group.
2. The groups should be as equal as possible.
3. Higher spending customers should go to higher groups.
`NTILE(n)` does exactly that — it divides the rows into `n` equal parts.
Correct Solution:
```sql
WITH customer_spending AS (
SELECT
u.id,
u.name,
SUM(o.total_price) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
)
SELECT
name,
total_spent,
NTILE(4) OVER (ORDER BY total_spent DESC) AS spending_quartile
FROM customer_spending
ORDER BY total_spent DESC;
```
Practice Question 1 (Medium)
Divide customers into 5 equal groups based on total spending (highest to lowest).
[Try it live →](https://sqlpractice.in/?question=ntile-basic)
Practice Question 2 (Hard)
Show the top 25% (quartile 1) customers by spending.
[Try it live →](https://sqlpractice.in/?question=ntile-top-quartile)
---
2. Aggregate Functions with OVER() (The Real Power)
You already know `SUM()`, `AVG()`, `COUNT()` with `GROUP BY`.
But when combined with `OVER()`, they become extremely powerful because they don’t collapse rows.
a) Running / Cumulative Aggregates
Example: Running total of sales per customer
```sql
SELECT
u.name,
o.ordered_at,
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;
```
Practice Question 3 (Easy)
Show running total of sales for each customer.
[Try it live →](https://sqlpractice.in/?question=running-total)
b) Moving Average / Rolling Average
Example: 3-order moving average of amount per customer
```sql
SELECT
u.name,
o.total_price,
AVG(o.total_price) OVER (
PARTITION BY u.id
ORDER BY o.ordered_at
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3
FROM orders o
JOIN users u ON o.user_id = u.id;
```
Practice Question 4 (Hard)
Calculate 3-order moving average for each customer.
[Try it live →](https://sqlpractice.in/?question=moving-average)
c) Compare with Overall / Group Average
Example: Show each order amount and how it compares to the customer’s average order value.
```sql
SELECT
u.name,
o.total_price,
AVG(o.total_price) OVER (PARTITION BY u.id) AS customer_avg,
o.total_price - AVG(o.total_price) OVER (PARTITION BY u.id) AS difference_from_avg
FROM orders o
JOIN users u ON o.user_id = u.id;
```
Practice Question 5 (Medium)
Show each order and its difference from the customer’s average order value.
[Try it live →](https://sqlpractice.in/?question=compare-with-average)
---
Complete Thinking Framework for Any Window Function Question
When you face any window function question, ask yourself these questions in order:
1. What do I need to calculate?
→ Sum, Average, Count, Rank, Previous/Next, Group division?
2. Should it reset for every group?
→ Use `PARTITION BY` (customer, department, month, etc.)
3. In what order should the calculation happen?
→ Use `ORDER BY` (usually date, id, or value)
4. Over which rows? (Frame clause)
→ `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` → Running total
→ `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` → Moving average
→ No frame → Entire partition
5. Which function fits best?
- Ranking → `RANK()`, `DENSE_RANK()`, `ROW_NUMBER()`
- Previous/Next → `LAG()`, `LEAD()`
- Divide into groups → `NTILE()`
- Aggregates across rows → `SUM()`, `AVG()`, `COUNT()` with `OVER()`
---
Final Thoughts
Window functions were one of the hardest topics for me initially.
The moment I stopped trying to memorize syntax and started asking:
- “What do I need to calculate?”
- “Should it reset per group?”
- “Over which rows?”
…everything became much clearer.
Window functions don’t just make queries faster — they make your thinking cleaner.
Go practice all these window function questions on [sqlpractice.in](https://sqlpractice.in) — especially the NTILE, moving average, and comparison ones.
You’ll feel the difference once you start thinking in “windows” instead of subqueries.
Keep going — you’re building real SQL confidence!
Building sqlpractice.in to help everyone master SQL for interviews — completely free.