[SQL 코딩테스트] 해커랭크 Aggregation 정답

FROM CITY 모음
⏰ 06:48

-- Revising Aggregations - The Count Function
SELECT COUNT(ID)
FROM CITY
WHERE POPULATION > 100000

-- Revising Aggregations - The Sum Function
SELECT SUM(POPULATION)
FROM CITY
WHERE DISTRICT = 'California'

-- Revising Aggregations - Averages
SELECT AVG(POPULATION)
FROM CITY
WHERE DISTRICT = 'California'

-- 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
⏰ 03:39

 

REPLACE 함수는 처음 써봤는데, 챗 GPT한테 물어보고 순식간에 익혀서 풀이 완료!

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

 

Top Earners
⏰ 13:46

 

풀어도 풀어도 헷갈리는 집계함수... 그리 어려운 문제는 아니었는데 이것저것 테스트해보느라 시간이 좀 걸렸다.

COUNT라는 집계함수를 쓰려면 반드시 GROUP BY를 써줘야 한다고 생각했는데, 정답2와 같이 1열에도 집계함수(MAX)를 써주면 꼭 그룹바이를 하지 않아도 결과가 문제없이 출력된다(!)

출력값의 수를 한 행으로 고정해줘서 문제없이 뽑히는 건가보다.

 

[정답 1] GROUP BY로 1열을 묶어버린 뒤, COUNT와 같이 뽑기

SELECT MONTHS*SALARY, COUNT(*)
FROM EMPLOYEE
WHERE MONTHS*SALARY IN (SELECT MAX(MONTHS*SALARY) FROM EMPLOYEE)
GROUP BY 1

 

[정답 2] COUNT와 같이 1열에 집계함수(MAX)를 넣어주기

SELECT MAX(MONTHS*SALARY), COUNT(*)
FROM EMPLOYEE
WHERE MONTHS*SALARY IN (SELECT MAX(MONTHS*SALARY) FROM EMPLOYEE)

 

Weather Observation Station 2,13,14,15,16,17,18
⏰ 6:24

-- 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 IN (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 IN (SELECT MIN(LAT_N)
                FROM STATION
                WHERE LAT_N > 38.7780)
                
-- Weather Observation Station 18
SELECT ROUND((MAX(LAT_N)-MIN(LAT_N))
	    +(MAX(LONG_W)-MIN(LONG_W)),4)
FROM STATION

 

Weather Observation Station 19
⏰ 17:22

 

제곱을 구하는 POWER 함수와, 제곱근을 구하는 SQRT 함수를 새롭게 배웠다! 

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

 

Weather Observation Station 20
⏰ 42:45

 

뭔가 복잡한 방법밖에는 떠오르지 않아서, 챗GPT에 MYSQL에서 MEDIAN 구하는 법 물어봤더니 LIMIT 안에서의 서브쿼리로 중간값만 뽑아내는 걸 알려줬는데, 아무리 해도 안돼서 집요하게 물고 늘어지니 LIMIT에서는 서브쿼리를 쓸 수 없단다 (?)

 

덕분에 LIMIT 와 OFFSET 기능에 대해서 깊이 파고들어볼 수 있었다. ^^ 고맙다 챗GPT야

결국 ROW_NUMBER 열을 덧붙인 후 ROW_NUMBER의 평균값을 구해 풀었다.

WITH LAT_ROW AS (
SELECT LAT_N, ROW_NUMBER() OVER(ORDER BY LAT_N) AS ROW_NUM
FROM STATION
)

SELECT ROUND(AVG(LAT_N),4)
FROM LAT_ROW
WHERE ROW_NUM = (SELECT FLOOR(AVG(ROW_NUM)) FROM LAT_ROW) OR 
      ROW_NUM = (SELECT CEIL(AVG(ROW_NUM)) FROM LAT_ROW)