What Are Window Functions and How to Use ROW_NUMBER(), RANK(), and NTILE()?
💡 Concept Name
SQL Window Functions – Functions that perform calculations across rows related to the current row, defined by an OVER clause.
📘 Quick Intro
Unlike aggregate functions that collapse rows, window functions preserve row-level detail while enabling advanced ranking and analytics. Common examples include ROW_NUMBER(), RANK(), and NTILE().
🧠 Analogy / Short Story
Think of a classroom where each student gets a report card with not just their grades, but also their class rank, roll number, and percentile. Window functions are like that — they add useful row-level context without hiding individual results.
🔧 Technical Explanation
- ROW_NUMBER(): Assigns a unique sequential number to each row in a partition.
- RANK(): Assigns rank based on order; ties get the same rank, and gaps are left.
- NTILE(n): Divides rows into
nequal groups (tiles) and assigns a group number. - All require an
OVER(PARTITION BY ... ORDER BY ...)clause to define the window frame. - These functions are ideal for pagination, leaderboards, and analytical summaries.
💻 Real Code Example
SELECT
EmployeeID, Department, Salary,
ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Salary DESC) AS RowNum,
RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS RankPos,
NTILE(4) OVER(ORDER BY Salary DESC) AS Quartile
FROM Employees;
❓ Interview Q&A
Q1: What is a window function?
A: A function that performs calculations across a set of rows related to the current row without collapsing the result set.
Q2: How is ROW_NUMBER() different from RANK()?
A: ROW_NUMBER() always produces unique row numbers; RANK() allows duplicates for ties and skips numbers accordingly.
Q3: What does NTILE() do?
A: It splits the result set into a specified number of equally distributed buckets or tiles.
Q4: Can you use window functions without PARTITION BY?
A: Yes, but all rows are treated as a single partition.
Q5: What is the OVER clause used for?
A: It defines the partition and order for the window function's calculation context.
Q6: Are window functions aggregate functions?
A: No, they compute values per row while aggregate functions summarize across rows.
Q7: Can you filter on ROW_NUMBER in WHERE clause?
A: No, use a CTE or subquery — it's computed after WHERE clause is processed.
Q8: What databases support window functions?
A: Most modern RDBMS: SQL Server, PostgreSQL, MySQL (8.0+), Oracle, etc.
Q9: What’s a use case for RANK()?
A: Displaying a leaderboard where multiple users can share the same rank.
Q10: Why use window functions?
A: For efficient row-wise analytics like running totals, ranks, and percentiles.
📝 MCQs
Q1. What does ROW_NUMBER() do?
- Groups rows
- Assigns unique number to each row
- Sums rows
- Deletes duplicates
Q2. What clause is required for a window function?
- GROUP BY
- HAVING
- OVER
- SELECT
Q3. What happens when two rows have the same value with RANK()?
- Same rank, next rank is skipped
- All get new numbers
- It errors out
- They get ROW_NUMBER
Q4. How does NTILE(4) work?
- Ranks in 4th order
- Deletes top 4 rows
- Divides result into 4 equal groups
- Adds 4 to rank
Q5. What does PARTITION BY do?
- Groups output
- Filters rows
- Separates rows into groups for window function
- Sorts columns
Q6. Which function always returns unique values?
- RANK()
- ROW_NUMBER()
- NTILE()
- SUM()
Q7. What clause defines sort order in window function?
- GROUP BY
- SELECT
- ORDER BY
- HAVING
Q8. Which of these is NOT a window function?
- RANK()
- NTILE()
- ROW_NUMBER()
- AVG()
Q9. Can window functions be combined with aggregates?
- No
- Yes, using OVER
- Only in MySQL
- Only in views
Q10. Is filtering with ROW_NUMBER() possible in WHERE?
- Yes
- No, use subquery or CTE
- Only in SELECT TOP
- Use HAVING
💡 Bonus Insight
Window functions go beyond ranking — they support moving averages, running totals, first/last value per group, and lag/lead analysis. Mastering them gives your SQL queries analytical superpowers.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!