[SQL 코딩테스트] 리트코드 Subqueries 정답

현업에서 서브쿼리보다 WITH구문을 더 선호한다는 이야기를 들었지만... 상황에 따라 적절한 구문을 잘 활용할 수 있는 게 최선일 테다. 처음으로 HARD가 있는 챕터. 화이팅!

 

[EASY] 1978. Employees Whose Manager Left the Company
LEFT JOIN / IS NULL
⏰ 8:30

 

LEFT JOIN RIGHT JOIN을 각각 어떤 상황에서 써야 하는지, 결과값이 어떻게 나오는지 아직도 약-간 헷갈린다.

반복 연습이 답!

SELECT E1.EMPLOYEE_ID
FROM EMPLOYEES E1
     LEFT JOIN EMPLOYEES E2
     ON E1.MANAGER_ID = E2.EMPLOYEE_ID
WHERE E1.SALARY < 30000
      AND E2.EMPLOYEE_ID IS NULL
      AND E1.MANAGER_ID IS NOT NULL
ORDER BY 1

 

[MEDIUM] 626. Exchange Seats
MOD / CASE 
⏰ 5:31

 

[배움 1] MOD 함수 = % 연산자

 

[정답 1] SELF JOIN으로 STUDENT 행 위아래로 밀고 붙여 출력하기

SELECT S1.ID, 
        CASE WHEN MOD(S1.ID,2) = 1 AND S2.ID IS NULL THEN S1.STUDENT
             WHEN MOD(S1.ID,2) = 1 THEN S2.STUDENT
             WHEN MOD(S1.ID,2) = 0 THEN S3.STUDENT
             END AS STUDENT
FROM SEAT S1
     LEFT JOIN SEAT S2
     ON S1.ID + 1 = S2.ID
     LEFT JOIN SEAT S3
     ON S1.ID - 1 = S3.ID
ORDER BY 1

 

[정답 2] ID 열을 무식하게(?) 더하고 빼서 STUDENT 열과 매칭한 뒤 정렬하기

SELECT CASE WHEN ID % 2 = 1 AND ID = (SELECT MAX(ID) FROM SEAT) THEN ID
            WHEN ID % 2 = 1 THEN ID + 1
            ELSE ID - 1
            END AS ID,
            STUDENT
FROM SEAT
ORDER BY 1

 

[MEDIUM] 1341. Movie Rating
UNION / 서브쿼리
⏰ 18:20

 

이젠... 답을 맞추는 것보다 남들이 얼마나 더 쉽게 풀었을까 얼른 보고싶어...

는 모두 이렇게 풀었다! 다른 값을 뽑은 다음 하나의 열, 하나의 테이블로 합칠 때는 UNION 이 최선인가보다.

 

[배움 1] UNION을 할 때 LIMIT를 함께 쓰고 싶으면 괄호로 묶어야 한다

(SELECT U.NAME AS RESULTS
FROM MOVIERATING R
     JOIN USERS U
     USING (USER_ID)
GROUP BY R.USER_ID
ORDER BY COUNT(*) DESC, U.NAME
LIMIT 1)

UNION ALL

(SELECT M.TITLE AS RESULTS
FROM MOVIERATING R
     JOIN MOVIES M
     USING (MOVIE_ID)
WHERE DATE_FORMAT(CREATED_AT, '%Y-%m') = '2020-02'
GROUP BY R.MOVIE_ID
ORDER BY AVG(RATING) DESC, M.TITLE
LIMIT 1)

 

[MEDIUM] 1321. Restaurant Growth
WINDOW
⏰ 29:01

 

[정답 1] 서브쿼리 안에서 윈도우 함수 활용하기

SELECT 
    DISTINCT VISITED_ON, 
    AMOUNT,
    ROUND(AMOUNT / 7, 2) AS AVERAGE_AMOUNT
FROM (SELECT VISITED_ON, 
        SUM(AMOUNT) OVER (
        ORDER BY VISITED_ON
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS AMOUNT,
        MIN(VISITED_ON) OVER() 1ST_DATE
     FROM CUSTOMER) A
WHERE VISITED_ON >= 1ST_DATE + INTERVAL 6 DAY

 

[정답 2] 본쿼리에서 윈도우함수 활용하고 LIMIT와 OFFSET으로 시작 범위 정하기

SELECT 
    DISTINCT VISITED_ON, 
    SUM(AMOUNT) OVER (
        ORDER BY VISITED_ON 
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS AMOUNT,
    ROUND(AVG(AMOUNT) OVER (
        ORDER BY VISITED_ON 
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW),2) AS AVERAGE_AMOUNT
FROM (SELECT VISITED_ON, SUM(AMOUNT) AS AMOUNT 
      FROM CUSTOMER
      GROUP BY VISITED_ON) C
LIMIT 6,1000

 

[배움 1] 윈도우 함수 범위 지정 시, ROWS BETWEEN 과 RANGE BETWEEN의 차이 / PRECEDING 과 FOLLOWING

 

 

[Medium] 602. Friend Requests II: Who Has the Most Friends
UNION ALL / WITH
⏰ 28:08

 

처음에 WITH구문으로 일단 DISTINCT ID의 리스트를 뽑아놓고... 옆에 각 열의 친구 수를 뽑은 다음 더해서 구하려고 했는데 그렇게 복잡하게 풀 필요가 하나도 없었다... 친구가 누구인지는 안 중요하고 그냥 COUNT만 하면 되니까 무식하게 각 ID가 몇번 쓰였는지만 합쳐서 세면 되는거였다. WOW...

 

문제를 보고 이렇게 쉽게 풀 수 있는 방법을 어떻게 떠올리지? 더 많이 풀어보면 되는걸까? 혹시 천재들은 딱 보면 떠오르는데 내가 못하고 있는 건 아닐까? 재능이 없는걸까?

(이상 진도를 나가면 나갈수록 오히려 자신감을 잃어가는 초보 SQL러의 합리적 의심이었습니다...

먼 훗날 돌아와서 이런 생각 했던 꼬꼬마 시절의 나를 귀여워하며 웃을 수 있는 날이 오길...) 

 

[정답 1] WITH구문과 LIMIT 활용해 풀기

WITH ALL_ID AS (
    SELECT REQUESTER_ID AS ID
    FROM RequestAccepted
    UNION ALL
    SELECT ACCEPTER_ID AS ID
    FROM RequestAccepted
)

SELECT ID, COUNT(*) AS NUM
FROM ALL_ID
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1

 

[정답 2] WITH구문과 RANK 윈도우함수 활용해 풀기

WITH ALL_ID AS (
    SELECT REQUESTER_ID AS ID
    FROM RequestAccepted
    UNION ALL
    SELECT ACCEPTER_ID AS ID
    FROM RequestAccepted
)

SELECT ID, NUM
FROM (SELECT ID, COUNT(*) AS NUM, 
             RANK() OVER (ORDER BY COUNT(*) DESC) AS RNK 
      FROM ALL_ID
      GROUP BY ID) A
WHERE RNK = 1

 

[Medium] 585. Investments in 2016
WITH / IN
⏰ 18:08

 

세상에... 그렇게 바로 다음 문제에서 나는 천재가 되었다! (감격)

...그리고 물론 더 쉽게 푸는 법이 있었다 ^~^

 

[정답 1] WITH구문으로 조건 2개 각각 임시 테이블화하기

WITH LIST_2015 AS (
    SELECT PID, COUNT(*) OVER (PARTITION BY TIV_2015) 2015_CNT
    FROM INSURANCE
),
LIST_CITY AS (
    SELECT PID, COUNT(*) OVER (PARTITION BY LAT, LON) CITY_CNT
    FROM INSURANCE
)

SELECT ROUND(SUM(TIV_2016),2) tiv_2016
FROM INSURANCE
WHERE PID IN (SELECT PID 
                FROM LIST_2015 A JOIN LIST_CITY B USING (PID)
                WHERE CITY_CNT = 1 AND 2015_CNT > 1
             )

 

[정답 2] WHERE 절에서 GROUP BY와 HAVING으로 조건 2개 바로 걸러내기

GROUP BY만 걸고 HAVING에서 저런식으로 대뜸 COUNT를 쓸 수 있는지 몰랐다...

뭘 썼을 때 어떤 건 SELECT에서 명시해줘야 하고, 어떤 건 FROM에서 한 번 더 걸러줘야 하고, 어떤 건 WHERE에서 바로 검색이 가능한지, 내가 명명한 열 이름이 어떤 순서로 만들어져 어디서 검색할 때 써먹을 수 있는건지 등등... 기본적인 작동조건 자체에서 아직 헷갈리는 부분이 많다. 연습만이 살길일세...

SELECT ROUND(SUM(TIV_2016),2) tiv_2016
FROM INSURANCE
WHERE TIV_2015 IN (SELECT TIV_2015 
                FROM INSURANCE
                GROUP BY TIV_2015
                HAVING COUNT(*) > 1
                ) AND
      (LAT,LON) IN (SELECT LAT, LON
                FROM INSURANCE
                GROUP BY LAT, LON
                HAVING COUNT(*) = 1
                )

 

[Hard] 185. Department Top Three Salaries
WITH / DENSE_RANK
⏰ 6:26

 

나 김난리... 천재로 거듭나다. (??)

리트코드의 50문제 중 유일한 HARD 난이도라 무지 X100 쫄았는데 5분만에 뚝딱 풀었다. 이러면 괜히 기분 좋으니까 난이도 시스템 다시 손보시라고요~!!

WITH LIST AS (SELECT D.NAME DEPARTMENT,
       E.NAME EMPLOYEE,
       SALARY,
       DENSE_RANK() OVER (PARTITION BY D.NAME ORDER BY SALARY DESC) RNK
FROM EMPLOYEE E
     JOIN DEPARTMENT D
     ON E.DEPARTMENTID = D.ID
)

SELECT Department, Employee, Salary 
FROM LIST
WHERE RNK <= 3
ORDER BY 1

---

 

헉헉. 오늘 문제 너무 어려워서 3문제 풀고 공부하는데 2시간 걸림. 오늘은 여기까지만 해야겠다.

 

-

리트코드 SQL 50문제 전부 풀이 완료~! 3주간 하루도 안 빼놓고 매일 풀었다는 게 스스로도 잘 믿기지 않는다. 왕뿌듯!

이 기세로 프로그래머스 남은 문제도 설 전까지 몽땅 부셔보자!