MySQL 해커랭크 | Top Earners ( WHERE절 MAX..? 집계함수? )

 

문제

https://www.hackerrank.com/challenges/earnings-of-employees/problem?isFullScreen=true

Top Earners | HackerRank

• earnings = salary * months
• find the maximum total earnings 
• count the total number of employees who have maximum total earnings

 

테이블

Employee

 

풀이과정

# earnings 최고치

SELECT MAX(salary*months) earnings FROM Employee

 

# 오답

WEHRE절에서 직접적으로 MAX 집계함수 사용 못 함!

설령 된다 하더라도 왼쪽 (salary * months)는 다중 행 값인데 MAX 결과값은 단일 값이라 = 으로 비교 안 됨 

SELECT  (salary * months) earnings, COUNT(name)
FROM Employee
WHERE  (salary * months) = MAX(salary*months)
GROUP BY earnings

 

# 정답

WHERE절에서 서브쿼리를 사용해 다중행 = 다중행 비교로 해결!

SELECT  (salary * months) earnings, COUNT(name)
FROM Employee
WHERE  (salary * months) = (SELECT MAX(salary*months) FROM Employee)
GROUP BY earnings