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

Need a 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

Need a 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

Need a 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

Need a hint?

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