[SQL][HackerRank] Population Density Difference

https://www.hackerrank.com/challenges/the-blunder/problem?h_r=next-challenge&h_v=zen&h_r=next-challenge&h_v=zen

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.:  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 < 10^5.

Sample Input

Sample Output

2061

Explanation

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

• actual average monthly salaries ==> AVG(Salary)
• salary without zeros ==> REPLACE(Salary, ‘0’, ‘’)
• miscalculated average monthly salaries ==> AVG(REPLACE(Salary, ‘0’, ‘’))
• difference between actual and miscalculated average salaries ==>
  SELECT AVG(Salary) - AVG(REPLACE(Salary, ‘0’, ‘’))
• round the difference up to next integer ==>
  SELECT CEIL(AVG(Salary) - AVG(REPLACE(Salary, ‘0’, ‘’)))
• from EMPLOYEES table ==> FROM EMPLOYEES

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