I'm trying to solve a SQL problem online that has 2 tables as following,
Employee table
The Employee table holds all employees.
+----+-------+--------+--------------+
| Id | Name | Sa1ary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+----+-------+--------+--------------+
Department table
The SQL should return the following data
I have the SQL queries as following,
SELECT D.Name AS Department, E.Name AS Employee, E.Salary AS Salary
FROM Employee E INNER JOIN Department D ON E.DepartmentId = D.Id
WHERE (SELECT COUNT(DISTINCT(Salary)) FROM Employee
WHERE DepartmentId = E.DepartmentId AND Salary > E.Salary) < 3
ORDER by E.DepartmentId, E.Salary DESC;
The SQL is good, but, I'm little confused about the < 3
part. Shouldn't it be = 3 as in the question it was asked about top 3 salaries ? I appreciate an explanation of the SQL and will be helpful.
Best Answer select rnk, last_name, department_id, salary from ( select last_name, department_id, salary, RANK () OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rnk from employees ) where rnk <= 3 ; You still need a sub-query, because analytic functions are computed after the WHERE clause is applied.
SELECT DEPARTMENT_NAME,AVG(SALARY) AS AVERAGE_SALARY FROM COMPANY GROUP BY DEPARTMENT_NAME; Query: SELECT DEPARTMENT_NAME,AVG(SALARY) AS AVERAGE_SALARY FROM COMPANY GROUP BY DEPARTMENT_NAME; Note: This query returns only 3 rows because the table has 3 departments.
If you added a column Count employees who earn more
your table would look like this
+----+-------+--------+--------------+-------------------------------+
| Id | Name | Sa1ary | DepartmentId | Count employees who earn more |
+----+-------+--------+--------------+-------------------------------+
| 1 | Joe | 70000 | 1 | 2 |
| 2 | Henry | 80000 | 2 | 0 |
| 3 | Sam | 60000 | 2 | 1 |
| 4 | Max | 90000 | 1 | 0 |
| 5 | Janet | 69000 | 1 | 3 |
| 6 | Randy | 85000 | 1 | 1 |
+----+-------+--------+--------------+-------------------------------+
Then to find the top 3 per dept. your WHERE would be
WHERE `Count employees who earn more` < 3
If you had =3
it would return the only employees that was the 4th highest
Since you don't have that column, that's what this SQL does
(SELECT COUNT(DISTINCT(Salary)) FROM Employee
WHERE DepartmentId = E.DepartmentId AND Salary > E.Salary)
If you wanted to produce the table described above you could do the following
SELECT
D.Name AS Department,
E.Name AS Employee,
E.Salary AS Salary,
Count(E2.Salary) as Count_employees_who_earn_more
FROM Employee E
INNER JOIN Department D
ON E.DepartmentId = D.Id
LEFT JOIN Employee E2 ON
e2.DepartmentId = E.DepartmentId
AND E2.Salary > E.Salary
GROUP BY D.Name ,
E.Name ,
E.Salary
Demo
I was working on the same SQL problem.
Just in case someone may need help.
Here's the answer I came up with.
SELECT
dpt.Name AS Department,
e1.Name AS Employee,
e1.Salary AS Salary
FROM Employee AS e1
INNER JOIN Department dpt
ON e1.DepartmentID = dpt.Id
WHERE 3 > (
SELECT COUNT(DISTINCT Salary)
FROM Employee AS e2
WHERE e2.Salary > e1.Salary
AND e1.DepartmentID = e2.DepartmentID
)
ORDER BY
Department ASC,
Salary DESC;
The hard part is to get the top 3 salaries of each department. I first count the [number of employees with a higher salary].
After that, I use 3 > [number of employees with a higher salary] to keep the top 3 salaries only. (If there are more than 3 employees in top 3, which is to say some of them have the same salary, all of them will be included.)
Query
SELECT *
FROM Employee e1
WHERE 3 > (
SELECT COUNT(DISTINCT Salary)
FROM Employee e2
WHERE e2.Salary > e1.Salary
AND e1.DepartmentID = e2.DepartmentID
);
Output
+------+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+------+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 6 | Randy | 85000 | 1 |
+------+-------+--------+--------------+
Then it's the easy part. You can just join this table with Department on DepartmentID to get the department name.
Final Output
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With