Mastering SQL Window Functions: ROW_NUMBER vs RANK vs DENSE_RANK

Mastering SQL Window Functions: ROW_NUMBER vs RANK vs DENSE_RANK

"In the symphony of SQL analytics, window functions are your virtuoso soloists—powerful, expressive, and precise."

Window functions are one of the most powerful tools in the SQL language. They allow you to perform advanced analytics across rows—without collapsing them. If you're building leaderboards, tracking trends, or running cumulative totals, ROW_NUMBER, RANK, and DENSE_RANK will become your best allies.

---

What Are Window Functions?

Window functions operate over a set of rows related to the current row, known as a window. Unlike aggregate functions (which collapse rows), window functions preserve the individual row context while performing calculations across the window.

Syntax Overview:

<function_name>() OVER (
  PARTITION BY column_name
  ORDER BY column_name
)

---

ROW_NUMBER(): Absolute Uniqueness

Assigns a unique number to each row based on the order defined.

SELECT Name, Department, Salary,
       ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RankBySalary
FROM Employees;

Use Case:

• Selecting the top-paid employee in each department
• Deleting duplicates by ordering and filtering

Note: Skips no values—always sequential (1, 2, 3...)

---

RANK(): Same Rank for Ties

RANK() assigns the same rank to tied rows but skips subsequent numbers.

SELECT Name, Score,
       RANK() OVER (ORDER BY Score DESC) AS RankPosition
FROM ContestResults;

Use Case:

• Leaderboards in competitions where equal scores get the same position

Example:
If two users are tied at Rank 1, the next gets Rank 3.

---

DENSE_RANK(): No Gaps in Ranking

DENSE_RANK() behaves like RANK() but does not skip numbers.

SELECT Name, Score,
       DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank
FROM ContestResults;

Use Case:

• Reporting where gaps in rank are not acceptable

Example:
If two users are tied at Rank 1, the next gets Rank 2.

---

Real-World Comparison Table

Name	Score	ROW_NUMBER	RANK	DENSE_RANK
Alice	98	1	1	1
Bob	98	2	1	1
Carlos	92	3	3	2
Diana	88	4	4	3

---

When to Use Which?

• ROW_NUMBER: When uniqueness matters (e.g., picking the latest row)
• RANK: When you care about ties and don’t mind gaps
• DENSE_RANK: When ties should be grouped but without gaps

---

Advanced Pattern: Top-N per Group

WITH Ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rnk
  FROM Employees
)
SELECT * FROM Ranked WHERE rnk <= 3;

This returns top 3 employees per department.

---

Bonus: Combine with Aggregates

Window functions can coexist with aggregates using PARTITION BY:

SELECT Name, Salary,
       SUM(Salary) OVER (PARTITION BY Department) AS TotalDeptSalary
FROM Employees;

Use this to enrich dashboards and data analytics without subqueries.

---

Final Thoughts: Think in Windows

Understanding window functions will change how you write SQL. You’ll stop thinking in rows, and start thinking in frames of reference—how each row relates to its peers.

In the art of SQL, mastering these functions is like learning harmony and counterpoint. It’s not just syntax—it’s data storytelling at scale.

---

Ready to dive deeper? Try combining window functions with LAG(), LEAD(), and NTILE() for full analytical power.

#SQL #AdvancedSQL #Analytics #Ranking