ROW_NUMBER, RANK, DENSE_RANK in PostgreSQL - Mini Project: Build & Apply

Using ROW_NUMBER, RANK, and DENSE_RANK in PostgreSQL

📖 Scenario: You work for a company that wants to analyze sales data to see how salespeople rank based on their monthly sales.The company wants to assign ranks to salespeople to identify top performers, handle ties properly, and also assign unique row numbers for each salesperson.

🎯 Goal: Build a PostgreSQL query that uses ROW_NUMBER(), RANK(), and DENSE_RANK() window functions to rank salespeople by their sales amount in descending order.

📋 What You'll Learn

Create a table called sales with columns salesperson (text) and sales_amount (integer).

Insert the exact sales data provided into the sales table.

Write a query that selects all columns and adds three new columns: row_num using ROW_NUMBER(), rank_num using RANK(), and dense_rank_num using DENSE_RANK(), all ordered by sales_amount descending.

Ensure the query orders the final output by sales_amount descending.

💡 Why This Matters

🌍 Real World

Ranking salespeople or items by performance is common in business reports and dashboards to identify top performers and handle ties correctly.

💼 Career

Understanding and using window functions like ROW_NUMBER, RANK, and DENSE_RANK is essential for data analysts and database developers to create advanced queries for reporting and analytics.

1

Create the sales table and insert data

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

PostgreSQL

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

Need a hint?

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

2

Set up the base SELECT query

Write a SELECT query that selects all columns from the sales table. This will be the base for adding ranking functions.

PostgreSQL

SELECT salesperson, sales_amount
FROM sales;
-- Your code here

Need a hint?

Use a simple SELECT statement to get started.

3

Add ROW_NUMBER(), RANK(), and DENSE_RANK() columns

Modify the SELECT query to add three new columns: row_num using ROW_NUMBER() OVER (ORDER BY sales_amount DESC), rank_num using RANK() OVER (ORDER BY sales_amount DESC), and dense_rank_num using DENSE_RANK() OVER (ORDER BY sales_amount DESC).

PostgreSQL

SELECT salesperson, sales_amount,
       ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS row_num,
       RANK() OVER (ORDER BY sales_amount DESC) AS rank_num,
       DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS dense_rank_num
FROM sales;
-- Your code here

Need a hint?

Use window functions with OVER (ORDER BY sales_amount DESC) to assign ranks.

4

Order the final output by sales_amount descending

Add an ORDER BY sales_amount DESC clause at the end of the SELECT query to sort the results by sales amount from highest to lowest.

PostgreSQL

SELECT salesperson, sales_amount,
       ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS row_num,
       RANK() OVER (ORDER BY sales_amount DESC) AS rank_num,
       DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS dense_rank_num
FROM sales
ORDER BY sales_amount DESC;
-- Your code here

Need a hint?

Use ORDER BY sales_amount DESC to sort the final results.