We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
My solution using PERCENTILE_CONT(0.5) worked, but I created this alternative solution to solidify my understanding of where the median comes from.
MS SQL:
Select Case When Count(MedianX) = 1 then cast(Sum(MedianX) as decimal(6,4)) else cast((Sum(MedianX))/2 as decimal(6,4)) end
From
(Select *, MedianX = Case
When Total%2 = 0 and Total/2 = R then Lat_N
When Total%2 = 0 and Total/2 = R - 1 then Lat_N
When Total%2 = 1 and Total/2 = R - 1 then Lat_N
Else 0
END
From (Select LAT_N, Row_Number() Over(Order by Lat_N) as R, Count(Lat_N) Over() as Total From Station) as X) as Y
Where MedianX <> 0
Group by Total;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Weather Observation Station 20
You are viewing a single comment's thread. Return to all comments →
My solution using PERCENTILE_CONT(0.5) worked, but I created this alternative solution to solidify my understanding of where the median comes from.
MS SQL:
Select Case When Count(MedianX) = 1 then cast(Sum(MedianX) as decimal(6,4)) else cast((Sum(MedianX))/2 as decimal(6,4)) end From
(Select *, MedianX = Case When Total%2 = 0 and Total/2 = R then Lat_N When Total%2 = 0 and Total/2 = R - 1 then Lat_N When Total%2 = 1 and Total/2 = R - 1 then Lat_N Else 0 END From (Select LAT_N, Row_Number() Over(Order by Lat_N) as R, Count(Lat_N) Over() as Total From Station) as X) as Y Where MedianX <> 0 Group by Total;