SQL Milestone 7: Window Functions 35 min interactive comprehensive guide⚡ Dialect: Standard ANSI SQL / PostgreSQL / MySQL 8.0+

SQL RANK(): Shared Tie Rankings & Gap-Based Leaderboards

Master competition ranking in SQL. Learn how RANK() OVER (...) assigns identical standings to tied values and skips subsequent positions, partition rankings by department or region, filter Top-N rank tiers using CTEs, and choose between RANK() and ROW_NUMBER().

⏱️ Estimated Time:35 Minutes

🎯 Level:Window Functions & Analytical Leaderboards

📊 Track:SQL for Data Analytics (Milestone 7)

✨ Mode:Live Interactive SQL Query Execution Lab

1. Core Concept & Skipped Rank Mechanics

RANK() calculates the rank for each row within a window partition. When two or more rows have identical values in the ORDER BY clause, they receive the exact same rank. Subsequent ranks are skipped:

SELECT
  employee_name,
  salary,
  RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

Salary: 90,000

Rank #1

Salary: 80,000 & 80,000

Rank #2 & #2 (Tied)

Salary: 70,000

Rank #4 (Rank #3 is skipped!)

2. 🔥 Live Interactive — Basic Ranking Lab

Observe how RANK() OVER (ORDER BY salary DESC) evaluates ties across all company employees:

RANK()	Emp ID	Employee Name	Department	Salary ₹	Tie Diagnosis
#1	103	Rahul	Sales	₹72,000	Tied for #1 (Skips #2)
#1	105	Arjun	Sales	₹72,000	Tied for #1 (Skips #2)
#3	106	Sneha	Finance	₹70,000	Single Earner
#4	102	Priya	Finance	₹65,000	Single Earner
#5	104	Neha	HR	₹60,000	Single Earner
#6	101	Amit	Sales	₹55,000	Single Earner

3. 🔥 Deep-Dive: Understanding Tied Values

Why Does Sneha Receive Rank #3 Instead of Rank #2?

Because both Rahul (₹72,000) and Arjun (₹72,000) share Rank #1. Since they occupy 2 physical rows, the 2nd position in the leaderboard is consumed. The next distinct salary (Sneha ₹70,000) automatically receives Rank #3.

4. ORDER BY Direction (DESC vs. ASC)

Toggle between DESC (highest = 1) and ASC (lowest = 1):

Query Executed: SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees;

5. 🔥 PARTITION BY Group Rankings

Rank employees within their respective departments independently:

Department	Employee Name	Salary ₹	Department Rank
Finance	Sneha	₹70,000	—
Finance	Priya	₹65,000	—
HR	Neha	₹60,000	—
Sales	Rahul	₹72,000	—
Sales	Arjun	₹72,000	—
Sales	Amit	₹55,000	—

6. 🔥 Live Practice — Top N With Ties

When filtering on WHERE sales_rank <= 2, notice that tied salespeople are all included:

Rank Threshold:

Region	Rank	Salesperson	Sales Amount ₹
West	#1	Amit	₹95,000
West	#2	Priya	₹90,000
West	#2	Rahul	₹90,000
North	#1	Neha	₹88,000
North	#1	Arjun	₹88,000

7. 🔥 RANK vs. ROW_NUMBER Comparison

Side-by-side comparison on tied sales values (₹10,000, ₹9,000, ₹9,000, ₹8,000):

Sales Amount ₹	ROW_NUMBER()	RANK()	Behavior Difference
10,000	1	1	Both assign 1 to leader
9,000 (Tied)	2	2 (Shared)	ROW_NUMBER splits tie; RANK preserves shared score
9,000 (Tied)	3	2 (Shared)	ROW_NUMBER gives 3; RANK keeps 2
8,000	4	4 (Skips 3)	RANK skips position #3

8. Real Business Case (Sales Performance Audit)

WITH regional_ranks AS (
  SELECT
    salesperson,
    region,
    sales_amount,
    RANK() OVER (
      PARTITION BY region
      ORDER BY sales_amount DESC
    ) AS sales_rank
  FROM sales
)
SELECT * FROM regional_ranks WHEREsales_rank <= 2;

9. 🔥 Live Debugging Challenge

Bug 1: Filtering RANK in WHERE

WHERE RANK() OVER (ORDER BY salary DESC) = 1

Issue: Window functions cannot be in WHERE. Wrap in a CTE first.

Bug 2: Wrong Tool for Exact Limit

Business requires exactly 3 rows for pagination, but developer wrote WHERE RANK() <= 3.

Issue: Ties will return 4+ rows. Use ROW_NUMBER() for strict pagination limits.

10. Business Requirement Decision Matrix

Test your architectural decision-making:

11. 🔥 Live Final Challenge (Top 3 Category Ranks)

Identify the top 3 sales ranks within every product category, including all qualifying ties:

Category	Rank	Product Name	Annual Sales ₹
Laptops	#1	Ultra Laptop 16	₹9,50,000
Laptops	#2	Pro Laptop 14	₹8,20,000
Laptops	#2	Gaming Laptop X	₹8,20,000
Laptops	#4	Air Slim 13	₹6,50,000
Monitors	#1	Studio Monitor 4K	₹4,50,000
Monitors	#1	UltraWide 38	₹4,50,000
Monitors	#3	Curved 32	₹3,80,000
Monitors	#4	Portable 16	₹2,20,000

12. RANK vs. DENSE_RANK Overview

Quick comparison on score sequence: [100, 90, 90, 80]:

Score	RANK()	DENSE_RANK()	Core Difference
100	1	1	Initial rank
90 (Tie)	2	2	Shared rank
90 (Tie)	2	2	Shared rank
80	4 (Skips 3)	3 (Gap-free)	RANK leaves gaps; DENSE_RANK is consecutive

13. Quick Check & Knowledge Assessment

TEST YOUR KNOWLEDGE

Knowledge Assessment: SQL RANK() Function

Test your mastery of tied evaluations, skipped positions, PARTITION BY leaderboards, and function selection.

Question 1 of 6Current Score: 0 / 0

1. What is the fundamental behavior of the SQL RANK() window function?

14. Accuracy & Official Specification

Official SQL Standard Specification:

Dialect Compatibility: ANSI SQL:2003, PostgreSQL 8.4+, MySQL 8.0+, SQLite 3.25+, SQL Server 2005+, Oracle 8i+.
Tie Formula: RANK = 1 + (number of rows preceding current row in partition having strictly better order values).