[SQL] 해커랭크 Top Earners

문제

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.

 

The Employee table containing employee data for a company is described as follows:

where employee_id is an employee's ID number, name is their name, months is the total number of months they've been working for the company, and salary is the their monthly salary.

 

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

Top Earners | HackerRank

예시

Sample Input

Sample Output

69952 1

 

Explanation

The table and earnings data is depicted in the following diagram:

The maximum earnings value is 69952. The only employee with earnings  is Kimberly, so we print the maximum earnings= 69952 value ( 69952) and a count of the number of employees who have earned $69952 (which is 1) as two space-separated values.

문제 풀이

SELECT months*salary AS earnings -- 문제에서 요구한 max earnings 구하기 위해 계산
      ,COUNT(*)					 
FROM Employee
GROUP BY earnings				 -- 같은 earnings를 가진 수 세기 위해 그룹 후 count
ORDER BY earnings DESC			 -- 가장 높은 earnings 출력을 위해 내림차순 정렬	
LIMIT 1							 -- 가장 상단에 위치한 1개 행만 출력

SELECT month * salary AS earnings,
	 , COUNT(*)
FROM Employee
WHERE month * salary = (SELECT MAX(month * salary) FROM Employee) -- SELECT에서 사용한 AS 는 WHERE절에서 사용불가
GROUP BY earnings

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

---

SQL을 독학하시는 분들에게 도움이 되길 바라며,

혹 더 좋은 방법이 있거나 오류가 있다면 편하게 말씀 부탁드립니다.