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

조인이 두렵지 않게 되는 그 날까지!

---

1378. Replace Employee ID With The Unique Identifier
#JOIN

SELECT U.UNIQUE_ID, E.NAME
FROM EMPLOYEES E
     LEFT JOIN EMPLOYEEUNI U
     ON U.ID = E.ID

 

1068. Product Sales Analysis I
#JOIN

SELECT p.product_name, s.year, s.price
FROM sales s
     JOIN product p ON s.product_id = p.product_id

 

1581. Customer Who Visited but Did Not Make Any Transactions
#JOIN #ISNULL

SELECT V.CUSTOMER_ID, COUNT(V.VISIT_ID) AS COUNT_NO_TRANS
FROM VISITS V
     LEFT JOIN TRANSACTIONS T
     ON V.VISIT_ID = T.VISIT_ID
WHERE T.TRANSACTION_ID IS NULL
GROUP BY V.CUSTOMER_ID

 

197. Rising Temperature
#JOIN #LAG #CROSSJOIN

 

풀이 방법이 다양해서 하나하나 뜯어보는 재미가 있었던 이번 문제. 시간을 투입하는 만큼 배움도 깊어진다.

 

[오답]

처음 풀었을 때는 호기롭게 LAG를 이용한 서브쿼리를 짰다가... 날짜 열에 하루라도 공백이 있는 경우 오류가 나서 탈락. 하루 전의 데이터만 비교하겠다는 날짜 조건이 필수로 필요했다.

SELECT ID
FROM (SELECT ID, RECORDDATE, TEMPERATURE, 
      LAG(TEMPERATURE) OVER (ORDER BY RECORDDATE)
      AS PRE_TEMP
      FROM WEATHER) W
WHERE TEMPERATURE > PRE_TEMP

 

[정답 1] 위드 구문 활용하기 (WITH + LAG) 

어제 스터디에서 한 분이 현업에서는 서브쿼리를 쓰면 코드가 복잡하고 인식하기 어렵게 되기 때문에 주로 WITH 구문을 활용한다는 말씀을 주셨었는데, 이렇게 바로 나올 줄 몰랐다...

덕분에 더 열심히 익혔다. 확실히 서브쿼리보다 훨씬 보기 좋아서 좋아!

WITH WEATHER_WITH_PRE AS (
SELECT ID, RECORDDATE, TEMPERATURE, 
      LAG(TEMPERATURE) OVER (ORDER BY RECORDDATE) AS PRE_TEMP,
      LAG(RECORDDATE) OVER (ORDER BY RECORDDATE) AS PRE_DATE
      FROM WEATHER
)
SELECT ID
FROM WEATHER_WITH_PRE
WHERE TEMPERATURE > PRE_TEMP AND 
      RECORDDATE = DATE_ADD(PRE_DATE, INTERVAL 1 DAY)

 

[정답 2] 조인 활용하기 (INNER JOIN) 

JOIN은 일반적으로 같은 값끼리 한 행으로 묶는 경우가 대부분인데, 이렇게 데이터에 조작을 가한 후 붙여 마치 LAG와 같이 쓸 수도 있다는 걸 배웠다. 잘 기억해뒀다가 필요할 때 꺼내 써야지~

 

* 잘 보면... 넷째줄의 DATEDIFF 구문은 위 정답 1의 마지막줄 DATE_ADD 구문으로 대체도 가능하다.

W1.RECORDDATE = DATE_ADD(W2.RECORDDATE, INTERVAL 1 DAY)

SELECT W1.ID
FROM WEATHER W1
     JOIN WEATHER W2
     ON DATEDIFF(W1.RECORDDATE, W2.RECORDDATE) = 1
WHERE W1.TEMPERATURE > W2.TEMPERATURE

 

[정답 3] 카다시안곱 활용하기 (CROSS JOIN)

자주 쓰지 않는 크로스조인이지만 이번 문제를 가장 심플하게 풀 수 있는 방법이었다.

현업에서도 실제로 (돌렸을 때 부하가 클) 크로스조인을 쓰는 경우가 있는지 궁금했다.

SELECT W1.ID
FROM WEATHER W1, WEATHER W2
WHERE W1.TEMPERATURE > W2.TEMPERATURE AND
      DATEDIFF(W1.RECORDDATE, W2.RECORDDATE) = 1

 

1661. Average Time of Process per Machine
#JOIN

 

이걸 맞추는 내가 신기해짐... 언제쯤 이게 맞나? 하는 불안함 없이 이거지. 하고 담담하고 자신있게 풀 수 있게 될까?

그래도 스스로가 대견하다. 위 문제처럼 조인으로 더 쉽게 풀 수 있는 방법은 없을까?

 

[정답 1] WITH 구문 활용하기

WITH ACTIVITY_TIME AS (
SELECT A1.MACHINE_ID, A1.PROCESS_ID, (A2.TIMESTAMP - A1.TIMESTAMP) AS TIME_AMOUNT
FROM ACTIVITY A1
     JOIN ACTIVITY A2
     ON A1.MACHINE_ID = A2.MACHINE_ID
     AND A1.PROCESS_ID = A2.PROCESS_ID
     AND A1.ACTIVITY_TYPE <> A2.ACTIVITY_TYPE
WHERE A1.ACTIVITY_TYPE = 'START'
)

SELECT MACHINE_ID, ROUND(AVG(TIME_AMOUNT),3) AS PROCESSING_TIME
FROM ACTIVITY_TIME
GROUP BY MACHINE_ID

[정답 2] JOIN 활용하기

그냥 WITH에 쓴 걸 그대로 밖으로 빼주기만 하면 되는 거였다. 그래도 뭔가 JOIN으로 한번에 짜려면 이게... 될까.... 제발.... 얍! 하는 느낌이라면 한 번 테이블을 정리하고 뽑는 WITH가 마음이 더 편한 이상한 느낌...

SELECT A1.MACHINE_ID, ROUND(AVG(A2.TIMESTAMP - A1.TIMESTAMP),3) AS PROCESSING_TIME
FROM ACTIVITY A1
     JOIN ACTIVITY A2
     ON A1.MACHINE_ID = A2.MACHINE_ID
     AND A1.PROCESS_ID = A2.PROCESS_ID
     AND A1.ACTIVITY_TYPE <> A2.ACTIVITY_TYPE
WHERE A1.ACTIVITY_TYPE = 'START'
GROUP BY A1.MACHINE_ID

 

577. Employee Bonus
#JOIN

 

생각보다 LEFT JOIN을 쓸 일이 많구나. INNER JOIN 쓸 일이 더 많을 줄 알았는데.

SELECT E.NAME, B.BONUS
FROM EMPLOYEE E
     LEFT JOIN BONUS B
     ON E.EMPID = B.EMPID
WHERE B.BONUS < 1000 OR B.BONUS IS NULL

 

1280. Students and Examinations
#WITH #JOIN

 

[정답 1. WITH 구문 활용하기]

무수한 시행착오 끝에 스스로 만들어낸 정답. 힌트 안 보고 풀면 엄청 뿌듯한데 시간도 그만큼 걸려서 이게 맞나 싶다...

WITH로 표를 여러개 만들 수 있다는 걸 알게 되었다.

WITH 
STUXSUB AS (
    SELECT *
    FROM STUDENTS, SUBJECTS
),

EXAM_COUNT AS (    
     SELECT  STUDENT_ID, SUBJECT_NAME, COUNT(SUBJECT_NAME) AS attended_exams
     FROM EXAMINATIONS
     GROUP BY 1,2
)

SELECT  S.STUDENT_ID, S.STUDENT_NAME, S.SUBJECT_NAME,
        IFNULL (ATTENDED_EXAMS, 0) AS attended_exams 
  FROM  STUXSUB S
        LEFT JOIN EXAM_COUNT E
        ON S.STUDENT_ID = E.STUDENT_ID
        AND S.SUBJECT_NAME = E.SUBJECT_NAME
 ORDER  BY 1,3

 

[정답 2. 중첩 JOIN 활용하기]

이렇게 쉽게 풀 수 있는 거였냐고... 나 JOIN 여러번 쓰기 왜이렇게 무서워하냐고.....

SELECT  S.STUDENT_ID, S.STUDENT_NAME, J.SUBJECT_NAME, 
        IFNULL(COUNT(E.SUBJECT_NAME),0) AS attended_exams
  FROM  STUDENTS S 
        CROSS JOIN SUBJECTS J
        LEFT JOIN EXAMINATIONS E
        USING (STUDENT_ID, SUBJECT_NAME)
 GROUP  BY 1,2,3
 ORDER  BY 1,3

 

570. Managers with at Least 5 Direct Reports
#WITH
⏰ 06:11

 

[정답 1. WITH 구문 활용하기]

WITH NUM AS (SELECT MANAGERID, COUNT(MANAGERID) AS REPORTS_NUM
FROM EMPLOYEE
GROUP BY MANAGERID
)

SELECT E.NAME
FROM EMPLOYEE E
     JOIN NUM N
     ON E.ID = N.MANAGERID
WHERE N.REPORTS_NUM >= 5

 

[정답 2. SELF JOIN과 HAVING 활용하기]

계산한 열을 따로 명시해주지 않으면 머릿속에 그려지지 않아서 불안해지는 나... 이렇게 쉽게 풀 수 있는데. SQL을 제대로 이해하고 쓰는 사람과 적당히 이해하고 쓰는 사람(=지금의 나)의 역량은 현격히 차이가 나겠다는 생각을 다시 한 번 한다.

 

GROUP BY에 의해 묶은 다음 조건을 걸고 싶으면 HAVING을 쓰자.

SELECT E1.NAME
FROM EMPLOYEE E1
     JOIN EMPLOYEE E2
     ON E1.ID = E2.MANAGERID
GROUP BY E2.MANAGERID
HAVING COUNT(E2.ID) >= 5

 

1934. Confirmation Rate
SUM+IF
⏰ 7:06

 

엣헴. 내가 이제 SUM(IF(~)) 사용법을 배워왔다 이말이야.

SELECT S.USER_ID, ROUND(SUM(IF(C.ACTION = 'CONFIRMED',1,0))/COUNT(S.USER_ID),2) AS CONFIRMATION_RATE
FROM SIGNUPS S
     LEFT JOIN CONFIRMATIONS C
     USING (USER_ID)
GROUP BY 1
ORDER BY 1

---

 

역시 조인... 이걸 월요일부터 넣다니 조금 실수였다 생각할 만큼 매콤했던 오늘의 진도. 그래도 반복하다보면 금방 늘 것 같아서 뿌듯하다.

나머지 문제는 내일의 진도로, 내일 추가해 수정 예정.

 

** 추가. 런타임의 배신... 똑같은 코드로 계속 돌려도 계속 천차만별로 다른 런타임이 나온다... 상위 10%에서 80% 수준까지...... 인터넷 속도 빠른 사람이 승자..? (신뢰도 상실)

그래도 클린코드를 증명해주는 최소한의 수단으로 붙여넣어본다.