https://www.hackerrank.com/challenges/the-blunder/problem

📔문제

Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her keyboard's 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.

The EMPLOYEES table is described as follows:

• Salary is per month.
• 1000<Salary<10^5.

---

📝예시

2061

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 , it does not get rounded up.

---

🧮분야

• AGGREGATION

---

📃SQL 코드

select ceil(avg(salary)-avg(zero))
from employees a,
    (select id, to_number(replace(to_char(salary),'0','')) zero
     from employees) b
where a.id=b.id;

---

📰출력 결과

---

📂고찰

round it up to the next integer.

나는 이게 반올림 하라는 뜻인줄 알았는데, 그게 아니고 다음 정수로 반환하는 거라서 올림해주라는 뜻이었다.

discussions를 보고 알았다..

replace함수를 이용해서 0을 다 제거한 테이블과 원래 테이블을 조인해서 해결했다.