solve median problem in hacker rand with example

console_2.sql

# https://www.hackerrank.com/challenges/weather-observation-station-20/problem
# Example on Sakila DB
# select case
#            when count(actor_id) % 2 = 0 then (
#                select sum(actor_id)
#                from (
#                         select *
#                         from (
#                                  select actor_id
#                                  from actor a
#                                  where (select count(actor_id) from actor b where b.actor_id < a.actor_id)
#                                            =
#                                        ((select count(actor_id) from actor c where c.actor_id > a.actor_id) +
#                                         1)
#                              ) ss
#                         union
#                         select *
#                         from (
#                                  select actor_id
#                                  from actor a
#                                  where ((select count(actor_id) from actor b where b.actor_id < a.actor_id) +
#                                         1)
#                                            =
#                                        (select count(actor_id) from actor c where c.actor_id > a.actor_id)
#                              ) dd
#                     ) ff
#            )
#            when count(actor_id) % 2 = 1 then (
#                select first_name
#                from actor a
#                where (select count(first_name) from actor b where b.first_name < a.first_name)
#                          = (select count(first_name) from actor c where c.first_name > a.first_name)
#            )
#            end esac
# from actor;

select case
#     if the row count is event, the median is the sum of the thr cells that are in the middle
           when count(LAT_N) % 2 = 0 then (
               select round(sum(LAT_N) / 2, 4)
               from (
                        select *
                        from (
                                 select LAT_N
                                 from station a
                                 where (select count(LAT_N) from station b where b.LAT_N < a.LAT_N)
                                           =
                                       ((select count(LAT_N) from station c where c.LAT_N > a.LAT_N) +
                                        1)
                             ) ss
                        union
                        select *
                        from (
                                 select LAT_N
                                 from station a
                                 where ((select count(LAT_N) from station b where b.LAT_N < a.LAT_N) +
                                        1)
                                           =
                                       (select count(LAT_N) from station c where c.LAT_N > a.LAT_N)
                             ) dd
                    ) ff
           )
#     if the row cound is odd, the median is the cell that is in between
           when count(LAT_N) % 2 = 1 then (
               select round(LAT_N, 4)
               from station a
               where (select count(LAT_N) from station b where b.LAT_N < a.LAT_N)
                         = (select count(LAT_N) from station c where c.LAT_N > a.LAT_N)
           )
           end esac
from station;