ArenaCTE: Top Employee Performance RankingMedium

CTE: Top Employee Performance Ranking

MediumSQLPractice library · +20 XPcommon table expressionssqlranking

A company wants to identify its top-performing employees based on sales data. They need a ranking of employees by their total sales amount. If two employees have the same total sales, they should have the same rank, and the next rank should be skipped (e.g., 1, 2, 2, 4).

Tables:

Employees (employee_id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(50))
Sales (sale_id INT PRIMARY KEY, employee_id INT, sale_amount NUMERIC(10, 2), sale_date DATE)

Return the employee's name, their department, and their performance rank, ordered by rank.

HOW TO HANDLE NULLS: If an employee has no sales, they should not appear in the results. If sale_amount is NULL for a sale, it should be treated as 0. If department is NULL, treat it as 'Unknown'. HOW TO HANDLE TIES: Employees with the same total sales amount receive the same rank, and the next rank is skipped. HOW TO HANDLE EMPTY RESULTS: If no sales data exists or no employee has sales, return an empty result set.

Tables

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50)
);

CREATE TABLE Sales (
    sale_id INT PRIMARY KEY,
    employee_id INT NOT NULL REFERENCES Employees(employee_id),
    sale_amount NUMERIC(10, 2),
    sale_date DATE NOT NULL
);

Example data

INSERT INTO Employees (employee_id, name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', 'Marketing'),
(3, 'Charlie', 'Sales'),
(4, 'David', 'HR'),
(5, 'Eve', 'Marketing'),
(6, 'Frank', NULL);

INSERT INTO Sales (sale_id, employee_id, sale_amount, sale_date) VALUES
(101, 1, 1500.00, '2023-01-15'),
(102, 3, 2200.00, '2023-01-20'),
(103, 1, 1800.00, '2023-02-10'),
(104, 2, 900.00, '2023-02-12'),
(105, 3, 2200.00, '2023-03-05'),
(106, 5, 3000.00, '2023-03-10'),
(107, 1, 1000.00, '2023-04-01'),
(108, 3, 1500.00, '2023-04-05'),
(109, 5, 2800.00, '2023-04-10'),
(110, 6, 500.00, '2023-05-01'),
(111, 4, NULL, '2023-05-05');

Your query is also checked on hidden datasets with different data.

CTE: Top Employee Performance Ranking

Tables

Example data

Hints

Similar problems