[HakcerRank] Top Earners

 

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

1. WHERE절 서브쿼리 이용

earnings의 가장 큰 값을 구한 후 WHERE절을 이용해서 그 값을 가진 사람 수를 필터링

SELECT salary * months AS earnings,
    COUNT(*)
FROM employee
WHERE salary * months = (
    SELECT MAX(salary*months)
    FROM employee
) -- WHERE 절에서 서브쿼리를 쓰고싶을때는 AS 로 불러올수가없다 ...
GROUP BY earnings

 

2. HAVING절 서브쿼리 이용

1번과 차이는 GROUP BY 후에 조건을 적어줬다는 것 !

그 외에는 전부 동일하

SELECT salary * months AS earnings,
    COUNT(*)
FROM employee
GROUP BY earnings
HAVING earnings = (
    SELECT MAX(salary * months)
    FROM employee
)

 

3. GROUP BY, ORDER BY, LIMIT 사

SELECT months*salary AS earnings
    , COUNT(employee_id)
FROM Employee
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1