RANK and DENSE_RANK difference in SQL - Mini Project: Build & Apply

Understanding the Difference Between RANK and DENSE_RANK in SQL

📖 Scenario: You work in a sales department and want to rank salespeople based on their monthly sales. You want to see how RANK and DENSE_RANK functions assign ranks when there are ties in sales amounts.

🎯 Goal: Create a simple sales table and write SQL queries to apply RANK() and DENSE_RANK() window functions to understand their differences in ranking tied values.

📋 What You'll Learn

Create a table called sales with columns salesperson and monthly_sales.

Insert exactly these rows into sales: ('Alice', 500), ('Bob', 700), ('Charlie', 700), ('David', 400).

Write a query using RANK() to rank salespeople by monthly_sales in descending order.

Write a query using DENSE_RANK() to rank salespeople by monthly_sales in descending order.

💡 Why This Matters

🌍 Real World

Ranking salespeople or products based on performance metrics is common in business reports and dashboards.

💼 Career

Understanding ranking functions helps in writing SQL queries for data analysis, reporting, and decision-making in many data-related jobs.

1

Create the sales table and insert data

Create a table called sales with columns salesperson (text) and monthly_sales (integer). Then insert these exact rows: ('Alice', 500), ('Bob', 700), ('Charlie', 700), and ('David', 400).

SQL

-- Create table sales and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Write a query using RANK() to rank salespeople

Write a SQL query that selects salesperson, monthly_sales, and a new column rank using the RANK() window function. Rank salespeople by monthly_sales in descending order.

SQL

CREATE TABLE sales (
    salesperson TEXT,
    monthly_sales INTEGER
);

INSERT INTO sales (salesperson, monthly_sales) VALUES
('Alice', 500),
('Bob', 700),
('Charlie', 700),
('David', 400);

-- Write a query using RANK() to rank salespeople
-- Your code here

Hint

Use RANK() OVER (ORDER BY monthly_sales DESC) to assign ranks.

3

Write a query using DENSE_RANK() to rank salespeople

Write a SQL query that selects salesperson, monthly_sales, and a new column dense_rank using the DENSE_RANK() window function. Rank salespeople by monthly_sales in descending order.

SQL

CREATE TABLE sales (
    salesperson TEXT,
    monthly_sales INTEGER
);

INSERT INTO sales (salesperson, monthly_sales) VALUES
('Alice', 500),
('Bob', 700),
('Charlie', 700),
('David', 400);

SELECT salesperson, monthly_sales,
       RANK() OVER (ORDER BY monthly_sales DESC) AS rank
FROM sales;

-- Write a query using DENSE_RANK() to rank salespeople
-- Your code here

Hint

Use DENSE_RANK() OVER (ORDER BY monthly_sales DESC) to assign dense ranks.

4

Compare RANK and DENSE_RANK results

Write a single SQL query that selects salesperson, monthly_sales, rank using RANK(), and dense_rank using DENSE_RANK(). Order the results by monthly_sales descending to clearly see the difference.

SQL

CREATE TABLE sales (
    salesperson TEXT,
    monthly_sales INTEGER
);

INSERT INTO sales (salesperson, monthly_sales) VALUES
('Alice', 500),
('Bob', 700),
('Charlie', 700),
('David', 400);

-- Write a query selecting salesperson, monthly_sales, RANK() as rank, and DENSE_RANK() as dense_rank
-- Order by monthly_sales descending
-- Your code here

Hint

Use both RANK() and DENSE_RANK() in the same query to compare results side by side.

Practice

1. What is the main difference between RANK() and DENSE_RANK() functions in SQL?

easy

A. DENSE_RANK() assigns random ranks, RANK() assigns sequential ranks.

B. RANK() skips rank numbers after ties, DENSE_RANK() does not skip numbers.

C. RANK() and DENSE_RANK() always return the same results.

D. DENSE_RANK() skips rank numbers after ties, RANK() does not skip numbers.

Solution

1. Step 1: Understand how RANK() works with ties

   RANK() assigns the same rank to tied rows but skips the next rank numbers accordingly. For example, if two rows tie for rank 1, the next rank will be 3.

2. Step 2: Understand how DENSE_RANK() works with ties

   DENSE_RANK() also assigns the same rank to tied rows but does not skip rank numbers. So after two rows tied at rank 1, the next rank will be 2.

3. Final Answer:

   RANK() skips rank numbers after ties, DENSE_RANK() does not skip numbers. -> Option B

4. Quick Check:

   Skipping ranks = RANK() [OK]

Hint: Remember: RANK skips numbers after ties, DENSE_RANK does not [OK]

Common Mistakes:

• Thinking both functions behave the same with ties
• Confusing which function skips rank numbers
• Assuming ranks are assigned randomly

2. Which of the following is the correct syntax to get the rank of employees ordered by salary using DENSE_RANK()?

easy

A. SELECT employee_id, RANK() BY salary FROM employees;

B. SELECT employee_id, DENSE_RANK(salary) FROM employees ORDER BY salary DESC;

C. SELECT employee_id, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;

D. SELECT employee_id, DENSE_RANK() ORDER BY salary FROM employees;

Solution

1. Step 1: Identify correct DENSE_RANK() syntax

   The correct syntax uses DENSE_RANK() OVER (ORDER BY column) to rank rows based on a column.

2. Step 2: Check each option

   SELECT employee_id, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM employees; uses the correct window function syntax. Options A, B, and D have syntax errors or misuse the function.

3. Final Answer:

   SELECT employee_id, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM employees; -> Option C

4. Quick Check:

   Window function syntax = SELECT employee_id, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM employees; [OK]

Hint: Use OVER (ORDER BY ...) with ranking functions [OK]

Common Mistakes:

• Omitting OVER clause
• Passing column inside function parentheses
• Using wrong keywords like BY instead of ORDER BY

3. Given the table Scores with values:
Player | Score
Alice | 100
Bob | 90
Carol | 90
Dave | 80
What will be the output of this query?

SELECT Player, Score, RANK() OVER (ORDER BY Score DESC) AS Rank, DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank FROM Scores;

medium

A. [{"Player":"Alice","Score":100,"Rank":1,"DenseRank":1},{"Player":"Bob","Score":90,"Rank":2,"DenseRank":2},{"Player":"Carol","Score":90,"Rank":2,"DenseRank":2},{"Player":"Dave","Score":80,"Rank":4,"DenseRank":3}]

B. [{"Player":"Alice","Score":100,"Rank":1,"DenseRank":1},{"Player":"Bob","Score":90,"Rank":3,"DenseRank":2},{"Player":"Carol","Score":90,"Rank":3,"DenseRank":2},{"Player":"Dave","Score":80,"Rank":4,"DenseRank":3}]

C. [{"Player":"Alice","Score":100,"Rank":1,"DenseRank":1},{"Player":"Bob","Score":90,"Rank":2,"DenseRank":3},{"Player":"Carol","Score":90,"Rank":2,"DenseRank":3},{"Player":"Dave","Score":80,"Rank":3,"DenseRank":4}]

D. [{"Player":"Alice","Score":100,"Rank":1,"DenseRank":1},{"Player":"Bob","Score":90,"Rank":2,"DenseRank":2},{"Player":"Carol","Score":90,"Rank":3,"DenseRank":2},{"Player":"Dave","Score":80,"Rank":4,"DenseRank":3}]

Solution

1. Step 1: Assign RANK() values

   Alice has highest score 100, rank 1. Bob and Carol tie at 90, both get rank 2. Because of tie at rank 2, next rank is 4 for Dave.

2. Step 2: Assign DENSE_RANK() values

   Alice is dense rank 1. Bob and Carol tie at 90, dense rank 2. Dave has next dense rank 3 (no skipping).

3. Final Answer:

   [{"Player":"Alice","Score":100,"Rank":1,"DenseRank":1},{"Player":"Bob","Score":90,"Rank":2,"DenseRank":2},{"Player":"Carol","Score":90,"Rank":2,"DenseRank":2},{"Player":"Dave","Score":80,"Rank":4,"DenseRank":3}] -> Option A

4. Quick Check:

   RANK skips numbers, DENSE_RANK does not [OK]

Hint: Check how ranks jump after ties for RANK vs DENSE_RANK [OK]

Common Mistakes:

• Assigning dense ranks with gaps
• Not skipping ranks in RANK after ties
• Mixing up rank numbers for tied rows

4. You wrote this query:

SELECT name, RANK() ORDER BY score DESC FROM players;

But it gives a syntax error. What is the problem?

medium

A. Missing OVER() clause after RANK() function.

B. ORDER BY cannot be used inside SELECT clause.

C. RANK() requires a parameter inside parentheses.

D. The table name is missing in the query.

Solution

1. Step 1: Check RANK() function usage

   RANK() is a window function and must be followed by OVER() clause specifying order.

2. Step 2: Identify missing OVER() clause

   The query uses RANK() without OVER(), causing syntax error.

3. Final Answer:

   Missing OVER() clause after RANK() function. -> Option A

4. Quick Check:

   Window functions need OVER() [OK]

Hint: Always add OVER() after window functions like RANK() [OK]

Common Mistakes:

• Forgetting OVER() clause
• Trying to put ORDER BY inside SELECT without OVER()
• Assuming RANK() takes parameters

5. You have a sales table:
Salesperson | SalesAmount
John | 500
Jane | 700
Joe | 700
Jill | 600
You want to assign ranks so that tied salespeople share the same rank and the next rank number is not skipped. Which query correctly achieves this?

hard

A. SELECT Salesperson, SalesAmount, RANK() OVER (ORDER BY SalesAmount DESC) AS Rank FROM sales;

B. SELECT Salesperson, SalesAmount, RANK() OVER (PARTITION BY SalesAmount ORDER BY SalesAmount DESC) AS Rank FROM sales;

C. SELECT Salesperson, SalesAmount, ROW_NUMBER() OVER (ORDER BY SalesAmount DESC) AS Rank FROM sales;

D. SELECT Salesperson, SalesAmount, DENSE_RANK() OVER (ORDER BY SalesAmount DESC) AS Rank FROM sales;

Solution

1. Step 1: Understand ranking requirements

   Tied salespeople should share the same rank, and the next rank number should not skip any numbers.

2. Step 2: Choose correct function

   DENSE_RANK() assigns same rank to ties and does not skip ranks. RANK() skips ranks after ties. ROW_NUMBER() never assigns same rank to ties. SELECT Salesperson, SalesAmount, RANK() OVER (PARTITION BY SalesAmount ORDER BY SalesAmount DESC) AS Rank FROM sales; misuses PARTITION BY.

3. Final Answer:

   SELECT Salesperson, SalesAmount, DENSE_RANK() OVER (ORDER BY SalesAmount DESC) AS Rank FROM sales; -> Option D

4. Quick Check:

   DENSE_RANK() for no skipped ranks after ties [OK]

Hint: Use DENSE_RANK() to avoid skipping ranks after ties [OK]

Common Mistakes:

• Using RANK() when no skipping is wanted
• Using ROW_NUMBER() which never ties
• Incorrect use of PARTITION BY in ranking