Write a query to rank employees based on salary.

Employee Salary Ranking

Here is the complete, production-ready SQL script. It sets up the table structure, populates it with realistic sample data (including identical salaries to demonstrate tie-handling), and runs the ranking query.

sql

-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

-- 2. INSERT SAMPLE DATA
-- Includes identical salaries in 'Sales' and 'Engineering' to showcase tie-handling
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice Smith', 'Engineering', 95000.00),
(2, 'Bob Jones', 'Sales', 75000.00),
(3, 'Charlie Brown', 'Engineering', 95000.00),
(4, 'Diana Prince', 'Marketing', 82000.00),
(5, 'Evan Wright', 'Sales', 75000.00),
(6, 'Fiona Gallagher', 'Engineering', 110000.00),
(7, 'George Clark', 'Sales', 75000.00),
(8, 'Hannah Abbott', 'Marketing', 68000.00);

-- 3. EXECUTE RANKING QUERY
SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    -- Unique row index per record
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    
    -- Traditional rank (Skips ranks after ties: 1, 2, 2, 2, 5...)
    RANK() OVER (ORDER BY salary DESC) AS standard_rank,
    
    -- Dense rank (No gaps in ranking order: 1, 2, 2, 2, 3...)
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Use code with caution.

Expected Query Output

employee_id	employee_name	department	salary	row_num	standard_rank	dense_rank
6	Fiona Gallagher	Engineering	110000.00	1	1	1
1	Alice Smith	Engineering	95000.00	2	2	2
3	Charlie Brown	Engineering	95000.00	3	2	2
4	Diana Prince	Marketing	82000.00	4	4	3
2	Bob Jones	Sales	75000.00	5	5	4
5	Evan Wright	Sales	75000.00	6	5	4
7	George Clark	Sales	75000.00	7	5	4
8	Hannah Abbott	Marketing	68000.00	8	8	5

Key Takeaways from the Data Visual

• Alice and Charlie both make $95,000.00. RANK places them both at 2, and skips 3. DENSE_RANK places them at 2, but the next person down (Diana) gets 3.
• Bob, Evan, and George all make $75,000.00. Notice how RANK skips all the way from 5 to 8 for the final row, while DENSE_RANK moves perfectly from 4 to 5.