Hacker Rank SQL Challenges

Problems

  1. Revising Aggregations - The Count Function
  2. Revising Aggregations - The Sum Function
  3. Revising Aggregations - Averages
  4. Average Population
  5. Japan Population
  6. Population Density Difference
  7. The Blunder
  8. Top Earners
  9. Weather Observation Station 2
  10. Weather Observation Station 13
  11. Weather Observation Station 14
  12. Weather Observation Station 15
  13. Weather Observation Station 16
  14. Weather Observation Station 17
  15. Weather Observation Station 18
  16. Weather Observation Station 19
  17. Weather Observation Station 20

Revising Aggregations - The Count Function

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select COUNT(name) from city.
  2. Filter population greater than 100000.

Solution:

SELECT COUNT(name) FROM city
WHERE population > 100000;

Revising Aggregations - The Sum Function

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select SUM(population) from city.
  2. Filter district equal to ‘california’.

Solution:

SELECT SUM(population) FROM city
WHERE LOWER(district) = 'california';

Revising Aggregations - Averages

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select AVG(population) from city.
  2. Filter district equal to ‘california’.

Solution:

SELECT AVG(population) FROM city
WHERE LOWER(district) = 'california';

Average Population

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select AVG(population) from city and round the value using ROUND function.

Solution:

SELECT ROUND(AVG(population)) FROM city;

Japan Population

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select SUM(population) from city.
  2. Filter countrycode equal to ‘JPN’.

Solution:

SELECT SUM(population) FROM city
WHERE UPPER(countrycode) = 'JPN';

Population Density Difference

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select difference MAX(population) and MIN(population) from city.

Solution:

SELECT MAX(population) - MIN(population) FROM city;

The Blunder

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select difference AVG(salary) and AVG(REGEXP_REPLACE(salary, '[0]')) from employees and using CEIL function round the result to next integer.

Solution:

SELECT CEIL(AVG(salary) - AVG(REGEXP_REPLACE(salary, '[0]'))) FROM employees;

Top Earners

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select product of months and salary and count of distinct employee_id.
  2. Group by product of months and salary
  3. Filter product of months and salary equal to MAX(product of months and salary) from employee table.

Solution:

SELECT months * salary, 
COUNT(DISTINCT employee_id) 
FROM employee
GROUP BY months * salary 
HAVING months * salary = (SELECT MAX(months * salary) FROM employee);

Weather Observation Station 2

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select sum of lat_n field and sum of long_w field ROUND upto 2 decimal.

Solution:

SELECT ROUND(SUM(lat_n),2), ROUND(SUM(long_w),2) FROM station;

Weather Observation Station 13

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select sum of lat_n field and ROUND upto 4 digits.
  2. Filter lat_n BETWEEN 38.7880 and 137.2345

Solution:

SELECT ROUND(SUM(lat_n),4) FROM station
WHERE lat_n BETWEEN 38.7880 AND 137.2345;

Weather Observation Station 14

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select max of lat_n field and ROUND upto 4 digits.
  2. Filter lat_n less than 137.2345

Solution:

SELECT ROUND(MAX(lat_n),4) FROM station
WHERE lat_n < 137.2345;

Weather Observation Station 15

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select long_w field and ROUND upto 4 digits.
  2. Filter lat_n equal to (MAX(lat_n) FROM station FILTER lat_n less than 137.2345)

Solution:

SELECT ROUND(long_w,4) FROM station
WHERE lat_n = (SELECT MAX(lat_n) FROM station WHERE lat_n < 137.2345);

Weather Observation Station 16

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select min of lat_n field and ROUND upto 4 digits.
  2. Filter lat_n greater than 38.7780.

Solution:

SELECT ROUND(MIN(lat_n),4) FROM station
WHERE lat_n > 38.7780;

Weather Observation Station 17

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select long_w field and ROUND upto 4 digits.
  2. Filter lat_n equal to (MIN(lat_n) FROM station FILTER lat_n greater than 38.7780)

Solution:

SELECT ROUND(long_w,4) FROM station
WHERE lat_n = (SELECT MIN(lat_n) FROM station WHERE lat_n > 38.7780);

Weather Observation Station 18

Problem -> URL
Difficulty Level: Medium

Refer Manhattan Distance
Pseudocode:

  1. Calculate Manhattan Distance using the formula and ROUND the result upto 4 digits.

Solution:

SELECT ROUND(ABS(MIN(lat_n) - MAX(lat_n)) + ABS(MIN(long_w) - MAX(long_w)), 4) 
FROM station;

Weather Observation Station 19

Problem -> URL
Difficulty Level: Medium

Refer Euclidean Distance
Pseudocode:

  1. Calculate Euclidean Distance using the formula and ROUND the result upto 4 digits.

Solution:

SELECT ROUND(SQRT(POWER((MIN(lat_n))-MAX(lat_n),2) + POWER((MIN(long_w)-MAX(long_w)),2)), 4) 
FROM station;

Weather Observation Station 20

Problem -> URL
Difficulty Level: Medium

Refer Median
Pseudocode:

  1. Calculate Median using the formula and ROUND the result upto 4 digits.

Solution:

SELECT ROUND(MEDIAN(lat_n), 4) FROM station;