Top Earners

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

• We define an employee's total earnings to be their monthly salary x 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 2 space-separated integers.

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

Lesson & Learned

1. MAX( )를 놓지 못한 잘못된 풀이

SELECT MAX(months * salary) AS max_earnings
     , COUNT(*)
FROM employee

2. 서브쿼리 이용한 풀이

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