Weather Observation Station 20

https://www.hackerrank.com/challenges/weather-observation-station-20

• A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to 4 decimal places.

SELECT ROUND(LAT_N, 4)
FROM (
    SELECT LAT_N
         , PERCENT_RANK() OVER (ORDER BY LAT_N) percent
    FROM STATION
) AS sub
WHERE percent = 0.5

Lesson & Learned

median(중앙값) 찾기 문제.
함수가 없어서 직접 구해야 한다.
window function 중 PERCENT_RANK( )를 사용했다. (MySql)

• 다른 풀이

-- 서브쿼리 대신 with문
WITH sub AS (SELECT LAT_N
                  , ROW_NUMBER() OVER (ORDER BY LAT_N) AS row_num
                  , COUNT(*) OVER () AS cnt
             FROM STATION
             )

-- 홀수, 짝수인 경우에 따른 중간 순위 계산
SELECT ROUND(AVG(LAT_N), 4)
FROM sub
WHERE CASE WHEN cnt % 2 = 1 THEN row_num = (cnt + 1) / 2
           ELSE row_num = cnt / 2
      END