[해커랭크] The Blunder

문제링크 : https://www.hackerrank.com/challenges/the-blunder/problem?isFullScreen=true

 

❓문제

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.:  average monthly salaries), and round it up to the next integer.

Input Format

The EMPLOYEES table is described as follows:

Note: Salary is per month.

Constraints

1000 < Salary < 10^5

Sample Input

Sample Output

 2061

 

Explanation

The table below shows the salaries without zeros as they were entered by Samantha:

Samantha computes an average salary of 98.00. The actual average salary is 2159.00.

The resulting error between the two calculations is 2159.00 - 98.00 = 2061.00. Since it is equal to the integer 2061, it does not get rounded up.

 

🔉 문제 설명

Samantha씨는 키보드가 고장이 난걸 월급 계산이 끝난 후에야 알게되었다.

1. 모든 직원들의 월급을 구하라.

2. 키보드의 0이 고장나서 0이 있을 때와 없을 때의 오차를 구하라

3. 올림해서 보여줘야 하기 때문에 0이 있는 원본 데이터에서 0이 없는 데이터를 빼서 평균 값을 구한 다음 CEIL() 함수 사용

❗답

SELECT CEIL(AVG(Salary) - AVG(replace(Salary,0,'')))
FROM EMPLOYEES

 

---

🤔풀이

replace를 사용해 0을 제