[MySQL] HackerRank : Top Earners Solution

https://www.hackerrank.com/challenges/earnings-of-employees/problem

Top Earners | HackerRank

 

 

1. FROM 절을 이용한 서브쿼리

SELECT tot_earnings, COUNT(employee_id)
FROM (
        SELECT employee_id, months * salary as tot_earnings
        FROM Employee
        WHERE months * salary = (SELECT MAX(months * salary) FROM Employee)
      )tmp
GROUP BY tot_earnings;

 

2. WHERE 절을 이용한 서브쿼리 

SELECT months * salary as tot_earnings
       , COUNT(*)
FROM Employee
WHERE months * salary = (SELECT MAX(months * salary) FROM Employee)
GROUP BY tot_earnings;

* alias 는 where 절에서 별칭을 인식하지 못함 

               group by 절에서는 사용 가능 

 

 

3. HAVING 절을 이용한 서브쿼리

SELECT months * salary as tot_earnings
       , COUNT(*)
FROM Employee
GROUP BY tot_earnings
HAVING tot_earnings = (SELECT MAX(months * salary) FROM Employee);

* alias 는 having 절에서도 사용 가능

 

4. ORDER BY, GROUP BY, LIMIT을 이용한 쿼리

SELECT months * salary as tot_earnings, COUNT(*)
FROM Employee
GROUP BY tot_earnings
ORDER BY tot_earnings DESC
LIMIT 1;

* alias 는 order by 절에서도 사용 가능

 

5. 오류 발생 원인

 

SELECT *
FROM (
        SELECT employee_id, months * salary as tot_earnings
        FROM Employee
      )tmp;
WHERE tot_earnings = (SELECT MAX(months * salary) as tot_earnings);

-- 문제: WHERE tot_earnings = (SELECT MAX(months * salary) as tot_earnings)를 서브쿼리로 넣어야지 MAX(months * salary) 값에 대한 COUNT를 할 수 있음 

 

SELECT *, COUNT(employee_id)
FROM (
        SELECT *, max(months * salary) as tot_earnings
        FROM Employee
      ) tmp
WHERE months * salary = (SELECT max(months * salary) FROM Employee)
GROUP BY months * salary;

-- 문제 : 일단 서브쿼리에서 max(months * salary)를 구할 수가 없음. => where 절로 처리해야

              *에 속하는 컬럼들은 GROUP BY에 적혀있지 않아서 에러 발생