HackerRank SQL - Aggregation (MySQL)

 

Solve SQL Code Challenges

 

Revising Aggregations - The Sum Function

SELECT SUM(POPULATION) FROM CITY WHERE DISTRICT = 'California';

 

 

Revising Aggregations - Averages

SELECT AVG(POPULATION) FROM CITY WHERE DISTRICT = 'California';

 

 

Revising Aggregations - The Count Function

SELECT COUNT(*) FROM CITY WHERE POPULATION > 100000;

 

 

Average Population

SELECT FLOOR(AVG(POPULATION)) FROM CITY;

 

 

Japan Population

SELECT SUM(POPULATION) FROM CITY WHERE COUNTRYCODE = 'JPN';

 

 

Population Density Difference

SELECT MAX(POPULATION) - MIN(POPULATION) FROM CITY;

 

 

The Blunder

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

 

 

Top Earners

SELECT MAX(salary * months) AS 'earnings', COUNT(*) FROM Employee 
GROUP BY salary * months ORDER BY earnings DESC LIMIT 1;

SELECT MAx(salary * months), COUNT(*) FROM Employee 
WHERE (salary * months) = (SELECT MAX(salary*months) FROM Employee);

 

 

Weather Observation Station 2

SELECT ROUND(SUM(LAT_N), 2), ROUND(SUM(LONG_W), 2) FROM STATION;

 

 

Weather Observation Station 13

SELECT ROUND(SUM(LAT_N), 4) FROM STATION WHERE LAT_N BETWEEN 38.7880 AND 137.2345;

 

 

Weather Observation Station 14

SELECT ROUND(MAX(LAT_N), 4) FROM STATION WHERE LAT_N < 137.2345;

 

 

Weather Observation Station 15

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

SELECT ROUND(MIN(LAT_N), 4) FROM STATION WHERE LAT_N > 38.7780;

 

 

Weather Observation Station 17

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

SELECT ROUND(ABS(MIN(LAT_N) - MAX(LAT_N)) + ABS(MIN(LONG_W) - MAX(LONG_W)), 4) FROM STATION;

 

Manhattan distance

 

 

Weather Observation Station 19

SELECT ROUND(SQRT(POW(MAX(LAT_N) - MIN(LAT_N), 2) + POW(MAX(LONG_W) - MIN(LONG_W), 2)), 4)
FROM STATION;

 

Euclidean distance - Wikipedia

 

 

Weather Observation Station 20

SET @rn = -1;
SELECT ROUND(AVG(LAT_N), 4) FROM (
    SELECT @rn := @rn + 1 AS RN, LAT_N FROM STATION ORDER BY LAT_N
    ) SUB
WHERE RN IN (FLOOR(@rn / 2), CEIL(@rn / 2));

 

Calculate Median in MySQL - GeeksforGeeks

 

[SQL] MySQL로 중앙값(Median) 찾아내기 - (HackerRank - Weather Observation Station 20 문제)