The Blunder in SQL | HackerRank Solution

Hello coders, today we are going to solve The Blunder HackerRank Solution in SQL.

The Blunder

Contents

Problem

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.

Input Format

The EMPLOYEES table is described as follows:

Column Type
IDInteger
NameString
SalaryInteger

Note: Salary is per month.

Constraints

  • 1000 < Salary < 10^5

Sample Input

IdNameSalary
1Kristeen1420
2Ashley2006
3Julia2210
4Maria3000

Sample Output

2061

Explanation

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

IdNameSalary
1Kristeen142
2Ashley26
3Julia221
4Maria3

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.

Solution – The Blunder in SQL

MySQL

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

Disclaimer: The above Problem (The Blunder) is generated by Hacker Rank but the Solution is Provided by CodingBroz. This tutorial is only for Educational and Learning Purpose.

1 thought on “The Blunder in SQL | HackerRank Solution”

  1. select cast(CEILING(avg(cast(salary as float)) – avg(cast(replace(salary,0,”) as float)))as int) from Employees

Leave a Comment

Your email address will not be published.