Description
Write a SQL query to find employees who earn one of the top three distinct salaries in each department. Return the department name, employee name, and salary, ordered by department name then by salary descending. **Output columns (in order):** department_name, employee_name, salary
Table:
employee| Column | Type |
|---|---|
| id | INT |
| name | TEXT |
| salary | INT |
| departmentId | INT |
Table:
department| Column | Type |
|---|---|
| id | INT |
| name | TEXT |
Examples
Input:
CREATE TABLE department (id INT, name TEXT); CREATE TABLE employee (id INT, name TEXT, salary INT, departmentId INT); INSERT INTO department VALUES (1, 'IT'); INSERT INTO employee VALUES (1, 'Joe', 70000, 1), (2, 'Jim', 90000, 1), (3, 'Henry', 80000, 1), (4, 'Sam', 60000, 1);Output:
IT|Jim|90000
IT|Henry|80000
IT|Joe|70000Explanation:
IT department has 4 employees. Top 3 earners: Jim (90000), Henry (80000), Joe (70000). Sam (60000) is excluded as 4th highest.
Input:
CREATE TABLE department (id INT, name TEXT); CREATE TABLE employee (id INT, name TEXT, salary INT, departmentId INT); INSERT INTO department VALUES (1, 'Sales'), (2, 'Marketing'); INSERT INTO employee VALUES (1, 'Alice', 85000, 1), (2, 'Bob', 92000, 1), (3, 'Carol', 78000, 1), (4, 'David', 88000, 1), (5, 'Eve', 95000, 1), (6, 'Frank', 72000, 2), (7, 'Grace', 68000, 2);Output:
Sales|Eve|95000
Sales|Bob|92000
Sales|David|88000
Marketing|Frank|72000
Marketing|Grace|68000Explanation:
Sales returns its top 3 earners. Marketing has only 2 employees, so both are returned.
Input:
CREATE TABLE department (id INT, name TEXT); CREATE TABLE employee (id INT, name TEXT, salary INT, departmentId INT); INSERT INTO department VALUES (1, 'Engineering'), (2, 'Finance'); INSERT INTO employee VALUES (1, 'John', 120000, 1), (2, 'Sarah', 120000, 1), (3, 'Mike', 115000, 1), (4, 'Lisa', 110000, 1), (5, 'Tom', 105000, 1), (6, 'Anna', 90000, 2), (7, 'Paul', 85000, 2), (8, 'Nina', 85000, 2);Output:
Engineering|John|120000
Engineering|Sarah|120000
Engineering|Mike|115000
Finance|Anna|90000
Finance|Paul|85000
Finance|Nina|85000Explanation:
Tied salaries share the same rank. All employees within the top 3 salary ranks per department are included.
Constraints
- •
Use window functions or subqueries