Mysql 문제풀이 강의 해설 SET1

📌 Weather Observation Station 17

출처: https://www.hackerrank.com/challenges/weather-observation-station-17/problem

Query the Western Longitude (LONG_W)where the smallest Northern Latitude (LAT_N) in STATION is greater than 38.7780. Round your answer to 4 decimal places.

[내가 작성한 답] 정답!!
SELECT ROUND(LONG_W,4)
FROM STATION
WHERE LAT_N > 38.7780
ORDER BY LAT_N 
LIMIT 1

📌 Contest Leaderboard

출저: https://www.hackerrank.com/challenges/contest-leaderboard/problem?isFullScreen=true

[내가 작성한 답] 정답!!
SELECT H.HACKER_ID, H.NAME, SUM(SUB.MAX_SCORE) TOTAL_SCORE
FROM (
	SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE) MAX_SCORE
	FROM SUBMISSIONS
	GROUP BY HACKER_ID, CHALLENGE_ID ) SUB
INNER JOIN HACKERS H ON SUB.HACKER_ID = H.HACKER_ID
GROUP BY H.HACKER_ID, H.NAME
HAVING TOTAL_SCORE > 0 -- TOTAL_SCORE != 0
ORDER BY TOTAL_SCORE DESC, H.HACKER_ID

📌 New Companies -- 다시 풀어보기!

출처: https://www.hackerrank.com/challenges/the-company/problem

우선 리드 매니저의 수를 구하는 쿼리를 작성해보자.

SELECT  COUNT(DISTINCT lead_manager_code)
FROM LEAD_MANAGER
WHERE COMPAY_CODE ='C9'

그런데 COMPANY_CODE가 C9인 것만 구하는게 아니니까

SELECT C.COMPANY_CODE -- [2] 너가 C9일 때
       ,C.FOUNDER
       , (SELECT  COUNT(DISTINCT lead_manager_code)
            FROM LEAD_MANAGER
            WHERE COMPANY_CODE = C.COMPANY_CODE) -- [1] 이렇게 작성하면 [3] C9으로 필터링
        , (SELECT  COUNT(DISTINCT SENIOR_MANAGER_code)
            FROM SENIOR_MANAGER
            WHERE COMPAY_CODE = C.COMPANY_CODE)
        , (SELECT  COUNT(DISTINCT manager_code)
            FROM MANAGER
            WHERE COMPAY_CODE = C.COMPANY_CODE)
        , (SELECT  COUNT(DISTINCT EMPLOYEE_code)
            FROM EMPLOYEE
            WHERE COMPAY_CODE = C.COMPANY_CODE)
FROM COMPANY C
ORDER BY COMPANY_CODE

📌 Occupations

출처: https://www.hackerrank.com/challenges/occupations/problem?isFullScreen=true

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 OCCUPATION
        , NAME
        , ROW_NUMBER () OVER (PARTITION BY OCCUPATION ORDER BY NAME) RN 
        -- 직업 별 sorted alphabetically 랭킹
    FROM OCCUPATIONS
) T
GROUP BY RN
ORDER BY RN