[HackerRank SQL] The Blunder

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

The Blunder | HackerRank

 

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 zeroes 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.

Input Format

The EMPLOYEES table is described as follows:

Note: Salary is measured in dollars per month and its value is .

Sample Input

Sample Output

2061

---

Answer

with mistake as(
    select *, replace(salary,0,'') as mistake_salary from EMPLOYEES) #실수한 값이 0을 제외한 것이므로 0을 다빼준다.(using salaries with any zeroes removed)
# the difference between her miscalculation and the actual average salary.
select ceil(avg(Salary) - avg(mistake_salary)) # round it up to the next integer.
from mistake;

 

Result

2253