Hello coders, today we are going to solve Weather Observation Station 20 HackerRank Solution in SQL.
Problem
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.
Input Format
The STATION table is described as follows:
| Field | Type |
|---|---|
| ID | NUMBER |
| CITY | VARCHAR2(21) |
| STATE | VARCHAR2(2) |
| LAT_N | NUMBER |
| Long_W | NUMBER |
where LAT_N is the northern latitude and LONG_W is the western longitude.
Solution – Weather Observation Station 20 in SQL
MySQL
SET @r = 0;
SELECT ROUND(AVG(Lat_N), 4)
FROM (SELECT (@r := @r + 1) AS r, Lat_N FROM Station ORDER BY Lat_N) Temp
WHERE
r = (SELECT CEIL(COUNT(*) / 2) FROM Station) OR
r = (SELECT FLOOR((COUNT(*) / 2) + 1) FROM Station)
Disclaimer: The above Problem (Weather Observation Station 20) is generated by Hacker Rank but the Solution is Provided by CodingBroz. This tutorial is only for Educational and Learning Purpose.
FOR ME, IT WORKED USING WINDOW FUNCTION:
SELECT CAST(LAT_N AS DECIMAL(20,4))
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY LAT_N DESC) AS NUM, LAT_N
FROM STATION
)
WHERE NUM = (SELECT FLOOR((COUNT(*)/2)+1) FROM STATION);
IN MS SQL
SELECT DISTINCT CONVERT(DECIMAL(15,4),PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY LAT_N) OVER ())
FROM STATION