Question

Samantha accidentally calculated the average salary by removing all zeros from the salary values. What is the difference between this miscalculated average and the actual average salary?(and round it up to the next intreger)

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

Solution

REPLACE(`CAST`(SALARY AS `VARCHAR`), '0', '')

First, delete ‘0’ in SALARY Column to save Samanth from her mistake.

But problem is…SALARY column is designated as INT Type. So I changed data type to ‘VARCHAR’ by using ‘CAST’

Problem solved, replace ‘0’ to ‘’ simply.

CAST(REPLACE(CAST(SALARY AS VARCHAR), '0', '') AS **DECIMAL**))

And then, change data type into ‘DECIMAL’ but why not ‘INT’?

If we used INT type, the decimal places would be discarded, which is not suitable when we need to accurately calculate decimal values.

CEILING(AVG(cast (SALARY as DECIMAL)) 
    - AVG(CAST(REPLACE(CAST(SALARY AS VARCHAR), '0', '') AS DECIMAL)))

Last part, we get the value with DECIMAL. so just round it up!

Today I’ve learned

TEXT TYPE

Type	Explanation	Example
CHAR	Fixed byte	char(5) → ‘123 ‘ (5byte)
VARCHAR	Felxible byte	varchar(5) → ‘123’ (3byte)
TEXT	very long text	no max size limit

INT TYPE

discard the decimal point and save/output only int

Type	Range	byte
`int`	-2,147,483,648 ~ 2,147,483,647	4
`smallint`	-32,768 ~ 32,767	2
`tinyint`	0 ~ 255	1
`biglint`	-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807	8

FLOAT TYPE

decimal/ numeric → calculate accurately
float/real → save or calculate data efficiently

Type	Range	byte
`decimal`	up to 38places	max 17bytes
`numeric`	up to 38places	max 17bytes
`float`	1.79E-308 ~ 1.79E+308	8
`real`	1.18E-38 ~ 3.40E+38	4

For the first time, I thought about efficiency of Database. Choose the right data type according to my circumstance

[SQL] HackerRank: The Blunder

Table of contents

Question

Solution

Today I’ve learned

Subscribe to my newsletter

Eun Lee

Eun Lee