HackerRank SQL: The Blunder외 1문제

03 October 2021 - less than 1 min read time
Tags: SQL TIL

The Blunder:

Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her keyboard’s 0 key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeros removed), and the actual average salary.

Write a query calculating the amount of error (i.e.: actual - miscalculated average monthly salaries), and round it up to the next integer.

실제 평균 월급과 키보드가 고장나서 잘못 계산된 평균 월급의 차를 계산하는 문제입니다.

Oracle

SELECT CEIL(AVG(salary) - AVG(REPLACE(salary, '0', '')))/*잘못 계산된 salary는 키보드가 0이 고장나 있기 때문에 0을 없앤다*/
FROM EMPLOYEES;

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.

salary와 months를 곱한 total earnings 중 max값을 찾고 그 max값에 속하는 employee의 수를 찾는 문제입니다.

select *
from (select salary * months, count(*)
     from employee
     group by salary * months
     order by salary * months desc /*salry*months로 내림차순 정렬 */)
where rownum = 1; /*내림차순으로 정렬한것 중 첫번째값 즉 가장 높은 값만 불러옵니다*/