[SQL 코딩테스트] 프로그래머스 JOIN 정답

[LV4] 주문량이 많은 아이스크림들 조회하기
UNION / LIMIT
⏰ 5:38

WITH ORDER_T AS (SELECT *
FROM FIRST_HALF
UNION ALL
SELECT *
FROM JULY
)

SELECT FLAVOR
FROM ORDER_T
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3

 

[LV4] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기
WITH / WHERE
⏰ 66:06

 

이렇게까지 오래 걸릴 문제는 아니었는데... 날짜 설정에서 애를 먹어서 시간을 많이 잡아먹었다.

 

이번에도 나는 WITH로 쉽게(?) 풀어버리려고 했는데, WHERE절에 서브쿼리를 써서 더 깔끔하게 푸신 분의 풀이를 같이 공유한다.

 

[정답1] 여러 조건을 WITH 구문 2개 활용해 풀기

WITH DURATION_FEE AS (
SELECT CAR_ID, CAR_TYPE, DAILY_FEE * 30 * (1-DISCOUNT_RATE/100) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
     LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
     USING (CAR_TYPE)
WHERE DURATION_TYPE = '30일 이상'
 ),
 YES_CAR AS (
 SELECT *
 FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
     LEFT JOIN CAR_RENTAL_COMPANY_CAR C
     USING (CAR_ID)
WHERE   CAR_ID NOT IN (
        SELECT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE END_DATE >= '2022-11-01' AND START_DATE <= '2022-11-30'
    )
     AND CAR_TYPE IN ('세단', 'SUV')
 )
 
 SELECT DISTINCT CAR_ID, CAR_TYPE, ROUND(FEE,0) FEE
 FROM YES_CAR
      LEFT JOIN DURATION_FEE 
      USING (CAR_ID, CAR_TYPE)
 WHERE FEE >= 500000 AND FEE < 2000000
 ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC

 

[정답2] 표 하나는 WHERE절의 서브쿼리를 이용해 거르기

SELECT
    A.CAR_ID,
    A.CAR_TYPE,
    ROUND(A.DAILY_FEE * 30 * (1 - B.DISCOUNT_RATE/100)) AS FEE
FROM
    CAR_RENTAL_COMPANY_CAR A
INNER JOIN
   CAR_RENTAL_COMPANY_DISCOUNT_PLAN B 
ON
    A.CAR_TYPE = B.CAR_TYPE
WHERE
    A.CAR_TYPE REGEXP '세단|SUV' AND
    B.DURATION_TYPE = '30일 이상' AND
    A.CAR_ID NOT IN (
        SELECT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE END_DATE >= '2022-11-01' AND START_DATE <= '2022-11-30'
    )
HAVING 
    FEE >= 500000 AND FEE < 2000000
ORDER BY
    FEE DESC, 
    A.CAR_TYPE ASC, 
    A.CAR_ID DESC

 

[LV4] 5월 식품들의 총매출 조회하기
SUM
⏰4:34

 

GROUP BY와 SUM을 사용해 쉽게 풀 수 있었던 문제!

SELECT PRODUCT_ID, PRODUCT_NAME, SUM(AMOUNT) * PRICE AS TOTAL_SALES
FROM FOOD_PRODUCT
     JOIN FOOD_ORDER
     USING (PRODUCT_ID)
WHERE DATE_FORMAT(PRODUCE_DATE, '%Y-%m') = '2022-05'
GROUP BY 1
ORDER BY 3 DESC, 1

[LV2] 조건에 맞는 도서와 저자 리스트 출력하기
JOIN / DATE_FORMAT
⏰ 2:25

SELECT B.BOOK_ID, A.AUTHOR_NAME, DATE_FORMAT(B.PUBLISHED_DATE, '%Y-%m-%d') PUBLISHED_DATE
FROM BOOK B
     JOIN AUTHOR A
     USING (AUTHOR_ID)
WHERE B.CATEGORY = '경제'
ORDER BY 3 ASC

[LV4] 그룹별 조건에 맞는 식당 목록 출력하기
WITH / COUNT
⏰14:01

 

풀긴 풀었는데... 가장 많은 리뷰를 작성한 사람들이 여러명이라면 그 사람들 리뷰가 다 나오도록 할 수 있어야 하는 거 아닌가! 하고 MAX를 이용해서 더 정확히 풀어보고 싶었지만 실패...

 

[정답1] WITH구문과 ORDER BY, LIMIT 활용해서 풀기

WITH MEM_REVIEW AS (
    SELECT MEMBER_ID, MEMBER_NAME, COUNT(MEMBER_ID) NUM
    FROM MEMBER_PROFILE P
     JOIN REST_REVIEW R
     USING (MEMBER_ID)
    GROUP BY 1
    ORDER BY 3 DESC
    LIMIT 1
)

SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM REST_REVIEW R
     JOIN MEM_REVIEW M
     USING (MEMBER_ID)
ORDER BY 3, 2

 

[정답2] 연쇄 WITH 활용하기!

WITH 2개 이상 쓰는 건 전에도 해봤지만, 위에서 만든 임시테이블을 두번째 WITH 구문에서 바로 쓸 수 있는줄 몰랐다!

내가 하고싶었던게 바로 이런 거였어...

WITH GET_CNT AS (
    SELECT MEMBER_ID, COUNT(REST_ID) AS CNT
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
), GET_MEMBER_ID AS (
    SELECT MEMBER_ID
    FROM GET_CNT 
    WHERE CNT = (
        SELECT MAX(CNT) 
        FROM GET_CNT
    )
)
SELECT M.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE,'%Y-%m-%d')
FROM MEMBER_PROFILE M
INNER JOIN REST_REVIEW R ON M.MEMBER_ID = R.MEMBER_ID
INNER JOIN GET_MEMBER_ID G ON M.MEMBER_ID = G.MEMBER_ID
ORDER BY R.REVIEW_DATE, R.REVIEW_TEXT

 

[LV3] 없어진 기록 찾기
RIGHT JOIN / IS NULL
⏰ 6:36

 

ON (USING) 안 걸고 왜 오류나는지 고민했던 나 ^^ 정신 차리자...

SELECT  O.ANIMAL_ID, O.NAME
  FROM  ANIMAL_INS I
        RIGHT JOIN ANIMAL_OUTS O
        USING (ANIMAL_ID)
 WHERE  I.ANIMAL_ID IS NULL

 

[LV3] 있었는데요 없었습니다
JOIN 
⏰ 2:09

 

여전히 알 수 없는 프로그래머스의 레벨 시스템... 첫날 풀었던 레벨 2가 더 어려웠던 것 같은데 그 사이에 내 실력이 늘어난걸로 (?)

SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_INS I
     JOIN ANIMAL_OUTS O
     USING (ANIMAL_ID)
WHERE I.DATETIME > O.DATETIME
ORDER BY I.DATETIME

 

[LV3] 오랜 기간 보호한 동물(1)
JOIN / LIMIT
⏰ 2:15

SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS I
     LEFT JOIN ANIMAL_OUTS O
     USING (ANIMAL_ID)
WHERE O.ANIMAL_ID IS NULL
ORDER BY I.DATETIME
LIMIT 3

 

[LV4] 보호소에서 중성화한 동물
LIKE
⏰ 3:26

 

NOT을 적절히 활용하기!

SELECT ANIMAL_ID, ANIMAL_TYPE, NAME
FROM ANIMAL_INS I
     JOIN ANIMAL_OUTS O
     USING (ANIMAL_ID, ANIMAL_TYPE, NAME)
WHERE SEX_UPON_INTAKE LIKE 'Intact%' and
      SEX_UPON_OUTCOME NOT LIKE 'Intact%'
ORDER BY 1

 

[LV2] 상품 별 오프라인 매출 구하기
JOIN / SUM / GROUP BY
⏰ 4:04

SELECT PRODUCT_CODE, SUM(P.PRICE * O.SALES_AMOUNT) AS SALES
FROM PRODUCT P
     JOIN OFFLINE_SALE O
     USING (PRODUCT_ID)
GROUP BY 1
ORDER BY 2 DESC, 1

 

[LV5] 상품을 구매한 회원 비율 구하기
서브쿼리 / DISTINCT / COUNT
⏰ 20:42

 

프로그래머스의 유일한 5레벨이라 무지 쫄았는데 그렇게 어려운 문제는 아니었다.

내가 조건을 제대로 이해 못하고 조금 헤맸을 뿐...

SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH,
       COUNT(DISTINCT USER_ID) AS PURCHASED_USERS,
       ROUND(COUNT(DISTINCT USER_ID)/ (SELECT COUNT(DISTINCT USER_ID)
                                 	FROM USER_INFO
                                 	WHERE YEAR(JOINED) = 2021), 1) AS PURCHASED_RATIO
FROM ONLINE_SALE S
     JOIN USER_INFO I
     USING (USER_ID)
WHERE YEAR(JOINED) = 2021
GROUP BY 1,2
ORDER BY 1,2

 

---

드디어 프로그래머스 전문제 풀이를 끝냈다!

아니 근데 나 다 끝냈는데 문제 세트에 총문제 수 더 많이 나와서 덜 끝낸것처럼 보이는거 뭐야뭐야...

 

아무튼 이정도면 실무에서 쓸 때 필요한 스킬 수준 (HOW) 은 갖추지 않았을까 싶다.

이제는 문제 정의 능력: 

WHEN 어떤 상황에

WHERE 어떤 데이터를 이용해

WHAT 어떤 값을 뽑아내야

WHY 목적을 달성하는데에 도움을 줄지

빠르게 잘 짜는 게 중요하지 않을까 하는 생각이다.

 

다음 스텝도 화이팅!