HackerRank(해커랭크) Weather Observation Station 17, Contest Leaderboard, Occupations

Weather Observation Station 17

    SELECT ROUND(LONG_W, 4)
      FROM STATION
     WHERE LAT_N = (
                    SELECT MIN(LAT_N)
                      FROM STATION
                     WHERE LAT_N > 38.7780
                   )

문제
Query the Western Longitude (LONG_W) where the smallest Northern Latitude (LAT_N) in STATION is greater than 38.7780

1. 38.7780 보다 큰 (초과) LAT_N 중
2. 가장 작은 LAT_N

영어 문제 주의할 점

greater than = ~보다 큰 = 초과
greater than or equal to = ~보다 크거나 같은 = 이상

Contest Leaderboard

풀이 1)

    SELECT SS.hacker_id, H.name, SS.score
      FROM    
            (
            SELECT MS.hacker_id, SUM(MS.score) AS score
            FROM
                (
                SELECT hacker_id, challenge_id, MAX(score) AS score
                FROM Submissions
                GROUP BY hacker_id, challenge_id
                ) MS
          GROUP BY MS.hacker_id
            ) SS
INNER JOIN Hackers H
        ON SS.hacker_id = H.hacker_id
     WHERE SS.score <> 0      
  ORDER BY SS.score DESC, SS.hacker_id

풀이 2)

         SELECT s.hacker_id
              , h.name
              , sum(s.score) AS total_score
           FROM (
                  SELECT hacker_id
                       , challenge_id
                       , max(score) AS score
                    FROM Submissions
                GROUP BY hacker_id, challenge_id
                ) s
LEFT OUTER JOIN Hackers h
             ON s.hacker_id = h.hacker_id 
       GROUP BY s.hacker_id, h.name
         HAVING total_score <> 0
       ORDER BY total_score DESC, s.hacker_id

Occupations

  SELECT MIN(CASE WHEN Occupation = 'Doctor' THEN NAME ELSE NULL END) Doctor
       , MIN(CASE WHEN Occupation = 'Professor' THEN NAME ELSE NULL END) Professor
       , MIN(CASE WHEN Occupation = 'Singer' THEN NAME ELSE NULL END) Singer
       , MIN(CASE WHEN Occupation = 'Actor' THEN NAME ELSE NULL END) Actor       
   FROM (
          SELECT Name
               , Occupation
               , ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RN
            FROM OCCUPATIONS
         ) T
GROUP BY RN
ORDER BY RN

윈도우 함수 ROW_NUMBER() 이용