Question

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:

Answer 1

WITH cte AS (

   SELECT s.*,

       ROW_NUMBER() OVER (ORDER BY lat_n) rn,

       COUNT(*) OVER () cnt

   FROM station s

)

SELECT AVG(lat_n)

FROM cte

WHERE

   (MOD(cnt, 2) = 0 AND rn IN (FLOOR(cnt/2), FLOOR(cnt/2) + 1)) OR

   (MOD(cnt, 2) = 1 AND rn = FLOOR(cnt/2) + 1);