[22.02.08] HackerRank 문제 풀이

Weather Observation Station 20

with t1 as
(select rank() over (order by lat_n) rnk, lat_n
from station)

select case
            when mod(count(*),2) = 1 then (select round(t1.lat_n,4) from t1 where t1.rnk = count(*)/2 + 0.5)
            when mod(count(*),2) = 0 then (select round(avg(t1.lat_n),4) from t1 where t1.rnk = count(*)/2
                                                                      or t1.rnk = count(*)/2+1)
            else 0
        end    
from t1

 

Top Competitors

/*
1.  print the respective hacker_id and name of hackers who achieved full scores for more than one challenge
2.  Order your output in descending order by the total number of challenges in which the hacker earned a full score.
3.  If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.
*/

with t2 as
(select s.submission_id,
        s.hacker_id,
        s.challenge_id,
        s.score,
        t1.max_score
from submissions as s
left join
(select c.challenge_id, d.score as max_score
from challenges as c
     left join difficulty as d on d.difficulty_level = c.difficulty_level) t1 on s.challenge_id = t1.challenge_id)

select t.hacker_id, h.name
from t2 as t
     left join hackers as h on t.hacker_id = h.hacker_id
where t.score = t.max_score
group by t.hacker_id, h.name
having count(*) > 1
order by count(*) desc, t.hacker_id

hackerrank에서 mysql을 사용하면 with문이 에러가 날 때가 있다.

이때, ms sql server를 사용하면 에러가 안 난다.

 

(문제 출처 - https://www.hackerrank.com/domains/sql)