Problem
Rank Employees By Department Salary
Medium- sql
- window-functions
Return every employee with a rank that orders the employees of their own department from the highest salary to the lowest. Rank 1 is the highest salary. Employees with equal salaries in the same department share a rank, and an employee with no salary comes last in the department.
Expected columns: name, dept, salary and rnk. Row order does not matter.
- Input
Employees(id, name, dept, salary) 1 | Alice | Sales | 50000 2 | Bob | Sales | 60000 3 | Carol | IT | 70000 4 | Dave | IT | NULL 5 | Eve | HR | 55000- Output
name | dept | salary | rnk Eve | HR | 55000 | 1 Carol | IT | 70000 | 1 Dave | IT | NULL | 2 Bob | Sales | 60000 | 1 Alice | Sales | 50000 | 2- Explanation
the rank starts again at 1 in every department. In IT, Carol out-earns Dave, and Dave's missing salary puts him second.
Sample database
Your query runs in the browser against these tables. Column names are exact.
Departments
- dept_id
- dept
Employees
- id
- name
- dept
- salary
Customers
- customer_id
- name
- city
- signup_date
Products
- product_id
- name
- category
- price
Orders
- order_id
- customer_id
- order_date
- status
OrderItems
- order_id
- product_id
- quantity
Logins
- customer_id
- login_date
Staff
- staff_id
- name
- title
- manager_id
No automatic grading here
Run your query and check the rows against what the question asks for. Then open the model answer to compare.
Tab indents. Press Esc, then Tab to leave the editor.
Loading the SQL engine…