[HackerRank] Aggregation > 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  decimal places.

 

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

---

[MySQL Solution 1]

SELECT ROUND(AVG(S.LAT_N), 4) 
FROM STATION S 
WHERE ABS((SELECT COUNT(*) FROM STATION WHERE LAT_N < S.LAT_N) -
          (SELECT COUNT(*) FROM STATION WHERE LAT_N > S.LAT_N)) <= 1;

 

[MySQL Solution 2]

SET @N := 0;

SELECT COUNT(*) FROM STATION INTO @TOTAL;

SELECT ROUND(AVG(A.LAT_N), 4)
FROM (SELECT @N := @N+1 AS ROW_ID, LAT_N FROM STATION ORDER BY LAT_N) A
WHERE
    CASE WHEN MOD(@TOTAL, 2) = 0 
            THEN A.ROW_ID IN (@TOTAL/2, (@TOTAL/2+1))
            ELSE A.ROW_ID = (@TOTAL+1)/2
    END;

 

 

 

문제 링크:

https://www.hackerrank.com/challenges/weather-observation-station-20/problem?isFullScreen=true&h_r=next-challenge&h_v=zen&h_r=next-challenge&h_v=zen&h_r=next-challenge&h_v=zen&h_r=next-challenge&h_v=zen&h_r=next-challenge&h_v=zen