Top Earning Employees
EasyPremium
Given the employee database with the schema shown below, write a query to fetch the top 3 earning employees, including their IDs, names and salaries.
employees +---------------+---------+ | id | int | | first_name | varchar | | last_name | varchar | | salary | int | | department_id | int | +---------------+---------+
Your query should output a result in the following format:
id | first_name | last_name | salary ----+------------+-----------+-------- int | varchar | varchar | int
This question tests your understanding of a few basic SQL concepts like the ORDER BY and LIMIT. Here's our complete solution:
SQLselect id, first_name, last_name, salary from employees order by salary desc limit 3;
Limit and rank() only works if there are no 2 employees with same salary ( which is okay for this use case) For the query to pass all the test results, we need to use dense_rank
with ranked_employees as ( select id, first_name, last_name, salary, dense_rank() over(order by salary desc) as salary_rank from employees ) select id, first_name, last_name, salary from ranked_employees where salary_rank <= 3