[고급 SQL 문제풀이] HackerRank 문제 및 풀이(1)

인프런 인강 참고

[백문이불여일타] 데이터 분석을 위한 고급 SQL 문제풀이 - 인프런 | 강의

 

 

 

[문제]

1. Weather Observation Station 17 : https://www.hackerrank.com/challenges/weather-observation-station-17/problem
2. Contest Leaderboard : https://www.hackerrank.com/challenges/contest-leaderboard/problem
3. New Companies : https://www.hackerrank.com/challenges/the-company/problem
   • Join을 사용하지 말고 다른 방법으로 풀어 보세요.
4. Occupations :c

 

 

 

 

 

 

 

 

 

 

 

 

 

[풀이]

1. Weather Observation Station 17

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

나는 위에 방식처럼 풀어서 결과를 도출해냈지만

인강에서 문제풀이는 나와 달랐다.

 

인강 문제풀이 결과 - 큰결과를 정렬하고 그중에 첫번째 값만 가져오기

SELECT ROUND(LONG_W,4) 
FROM STATION
WHERE LAT_N > 38.7780
ORDER BY LAT_N
LIMIT 1

 

2. Contest Leaderboard

/*
The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of 0  from your result.
*/

SELECT H.hacker_id , H.name , SUM(A.score) AS totalScore
FROM
(
SELECT S.hacker_id, S.challenge_id , MAX(S.SCORE) AS score  FROM Submissions S
GROUP BY S.hacker_id, S.challenge_id) A , Hackers H
WHERE A.hacker_id = H.hacker_id
GROUP BY H.hacker_id, H.name
having totalScore !='0'
ORDER BY totalScore DESC, H.hacker_id ASC

 

3.  New Companies 

/*
 write a query to print the company_code, founder name, total number of lead managers, 
 total number of senior managers, total number of managers, and total number of employees. 
 Order your output by ascending company_code.
*/

SELECT C.company_code, C.founder,
        count(distinct LM.lead_manager_code) AS totLeadManager , 
        count(distinct SM.senior_manager_code) AS totSeniorManager , 
        count(distinct M.manager_code) AS totManager ,
        count(distinct E.employee_code) AS totEmployee
FROM Company C , Lead_Manager LM  , Senior_Manager SM  ,  Manager M , Employee E
WHERE  C.company_code =LM.company_code
AND LM.lead_manager_code = SM.lead_manager_code
AND SM.senior_manager_code = M.senior_manager_code
AND M.manager_code = E.manager_code
GROUP BY C.company_code, C.founder
ORDER BY C.company_code

  서브쿼리로 푸는 방법

/*
 write a query to print the company_code, founder name, total number of lead managers, 
 total number of senior managers, total number of managers, and total number of employees. 
 Order your output by ascending company_code.
*/

SELECT C.company_code, C.founder,
        (SELECT count(distinct LM.lead_manager_code) FROM Lead_Manager LM 
         WHERE C.company_code = LM.company_code  ) AS totLeadManager , 
          (SELECT  count(distinct SM.senior_manager_code) FROM Senior_Manager SM
         WHERE C.company_code = SM.company_code  ) AS totSeniorManager , 
          (SELECT count(distinct M.manager_code) FROM Manager M
         WHERE C.company_code = M.company_code  ) AS totManager , 
          (SELECT count(distinct E.employee_code) FROM Employee E
         WHERE C.company_code = E.company_code  ) AS totEmployee               
FROM Company C
ORDER BY C.company_code

 

4. Occupations ***** 어려웠음

 - Case를 활용한 피벗 테이블 

 

첫번째 컬럼: Doctor , 두번째 컬럼 : Professor, 세번째 컬럼 : Singer, 네번째 컬럼: Actor

 

- ROW_NUMBER() OVER ( PARTITION BY ...ORDER BY ) 사용... <- 오라클의 경우에는 이 window함수로 끝!

- mysql의 경우 ROW_NUM 구현하기

SELECT @RNUM := @RNUM + 1 AS RNUM             , A.*     FROM 테이블 A            , (SELECT @RNUM := 0) B

- mysql의 경우 ROW_NUMBER(PARTITION BY 컬럼1 ORDER BY 컬럼2) 구현하기

SELECT @ROW_NUM := IF(@PREV_VALUE = A.컬럼1, @ROW_NUM + 1, 1) AS ROW_NUMBER           , @PREV_VALUE := A.컬럼1           , A.*   FROM 테이블 A          , (SELECT @ROW_NUM := 1) X          , (SELECT @PREV_VALUE := '') Y  ORDER BY A.컬럼2

- CASE 문을 MIN() 으로  감싼 이유 : MAX() 나 MIN() 에서 NULL 은 비교대상으로 삼지 않는다. 

SELECT MIN(CASE WHEN A.occupation = 'Doctor' THEN A.name else null END) doctor ,
       MIN(CASE WHEN A.occupation = 'Professor' THEN A.name else null END) professor ,
       MIN(CASE WHEN A.occupation = 'Singer' THEN A.name else null END) singer ,
       MIN(CASE WHEN A.occupation = 'Actor' THEN A.name else null END) actor     
FROM (
    SELECT 
        occupation, 
        name, 
        -- ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) RNUM    
        @rownum := IF(@value=occupation,@rownum+1,1) AS RNUM  ,     
        @value := occupation
    FROM OCCUPATIONS, 
        (SELECT @rownum :=0) AS R , -- @rownum 0 reset 
        (SELECT @value := '') AS P
    ORDER BY occupation, name
) A  
GROUP BY A.RNUM