[해커랭크(Hacker Rank)] Top Earners (MySQL)

문제

We define an employee's total earnings to be their monthly salary * months worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table.
Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as  space-separated integers.

코드

SELECT * FROM

SELECT salary * months as earnings, count(*)
FROM employee
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1

1. total earnings은 salary * months로 정의
2. total earnings를 계산해놓고 그 중에서 제일 많이 번 사람을 maximum total earnings로 정의
   MAX(salary*months)로 했다가 오류가 남 > 저 상태에서 max는 불가능! 
3. 그래서 가장 잘 버는 사람이 몇 명인지 구하기 > LIMIT 1

FROM 절 서브쿼리

SELECT MAX(sub.earning), sub.cnt
FROM (
    SELECT salary * months as earning
         , count(*) cnt
    FROM Employee
    GROUP BY earning
) sub
GROUP BY sub.cnt
ORDER BY max(sub.earning) DESC
LIMIT 1

이럴거면 subquery를 안쓰는 게 낫지 않나.. 생각하다가 WHERE 절에 써보자 생각함

WHERE 절 서브쿼리

SELECT salary * months as earnings, count(*)
FROM Employee
WHERE salary * months = (SELECT max(salary * months) From Employee)
GROUP BY earnings

sarary * months 의 조건으로 max값을 줬더니 count도 잘 뽑힌다

HAVING절 서브쿼리

SELECT months * salary as earnings
	 , count(*)
FROM Employee
GROUP BY earnings
HAVING earnings = (SELECT max(months*salary) FROM employee)

출처