[프로그래머스] SQL 고득점 Kit 문제풀이 - JOIN

프로그래머스 스쿨 - 온라인 IT 특화 교육 전문 플랫폼

조건에 맞는 도서와 저자 리스트 출력하기(Lv. 2)

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

• INNER JOIN, LEFT JOIN, RIGHT JOIN 무엇을 사용하든 결과에는 영향을 미치지 않는다(별다른 조건이 없기 때문에)

5월 식품들의 총매출 조회하기(Lv. 4)

SELECT A.PRODUCT_ID, A.PRODUCT_NAME, 
SUM(A.PRICE * B.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT AS A
JOIN FOOD_ORDER AS B
ON A.PRODUCT_ID = B.PRODUCT_ID
WHERE B.PRODUCE_DATE LIKE '2022-05%'
GROUP BY A.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, PRODUCT_ID ASC;

• 생산일자가 2022년 5월인 식품 → WHERE
• 총매출 → GROUP BY A.PRODUCT_ID , SUM(A.PRICE * B.AMOUNT) == SUM(B.AMOUNT) * A.PRICE
  • 총매출 = (식품 가격 * 주문량) 총합

주문량이 많은 아이스크림들 조회하기(Lv. 4)

1. LEFT JOIN + 서브쿼리

-- 맛별 (상반기 + 7월) 총 주문량 내림차순
SELECT A.FLAVOR
FROM FIRST_HALF AS A
LEFT JOIN (
    -- 7월의 맛별 총 주문량
    SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
    FROM JULY
    GROUP BY FLAVOR) AS B
ON A.FLAVOR = B.FLAVOR
ORDER BY (A.TOTAL_ORDER + B.TOTAL_ORDER) DESC
LIMIT 3;

• 7월에는 아이스크림 주문량이 많아 같은 아이스크림에 대하여 서로 다른 두 공장에서 아이스크림 가게로 출하를 진행하는 경우가 있습니다. 이 경우 같은 맛의 아이스크림이라도 다른 출하 번호를 갖게 됩니다. → 7월의 FLAVOR 별 총 주문량을 먼저 구함
• FIRST_HALF 테이블에 있는 맛이 JULY 테이블에는 없는 경우를 고려하여 JOIN 이 아니라 LEFT JOIN 을 사용했지만 실제로 JOIN 으로 해도 정답 처리는 된다.

1. LEFT JOIN

SELECT A.FLAVOR
FROM FIRST_HALF AS A
LEFT JOIN JULY AS B
ON A.FLAVOR = B.FLAVOR
GROUP BY B.FLAVOR
ORDER BY SUM(A.TOTAL_ORDER + B.TOTAL_ORDER) DESC
LIMIT 3;

1. UNION ALL + 서브쿼리

SELECT FLAVOR
FROM (
    SELECT FLAVOR, TOTAL_ORDER
    FROM FIRST_HALF
    UNION ALL
    SELECT FLAVOR, TOTAL_ORDER
    FROM JULY
) AS A
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3;

• FIRST_HALF 와 JULY 의 FLAVOR, TOTAL_ORDER 을 UNION ALL 로 중복없이 구한다.
• 
• FLAVOR 로 GROUP BY 하고 SUM(TOTAL_ORDER) 값을 내림차순으로 정렬한다.

그룹별 조건에 맞는 식당 목록 출력하기(Lv. 4)

리뷰를 가장 많이 작성한 회원이 여러 명인 경우를 고려하여 새롭게 풀었다.

1. 서브쿼리 풀이

SELECT A.MEMBER_NAME, B.REVIEW_TEXT, 
DATE_FORMAT(B.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE AS A
JOIN REST_REVIEW AS B
ON A.MEMBER_ID = B.MEMBER_ID
WHERE A.MEMBER_ID = (
    -- 1. 리뷰를 가장 많이 쓴 회원의 ID
    SELECT MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    ORDER BY COUNT(MEMBER_ID) DESC
    LIMIT 1
)
ORDER BY REVIEW_DATE, REVIEW_TEXT;

• 리뷰를 가장 많이 작성한 회원이 여러명일 수 있다고 생각했는데, 여기서는 그냥 LIMIT 1로 한명만 뽑아내는 것을 정답으로 처리하는 것 같다.
• 리뷰를 가장 많이 작성한 회원 → GROUP BY, COUNT, LIMIT 으로 해결(서브쿼리)
• 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력 → JOIN

1. WITH 절

WITH TBL AS (
        -- 1. 리뷰를 가장 많이 쓴 회원의 ID
    SELECT MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    ORDER BY COUNT(*) DESC
    LIMIT 1
)
SELECT MP.MEMBER_NAME, RR.REVIEW_TEXT, 
DATE_FORMAT(RR.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE AS MP
JOIN REST_REVIEW AS RR
ON MP.MEMBER_ID = RR.MEMBER_ID
WHERE MP.MEMBER_ID = (SELECT MEMBER_ID FROM TBL)
ORDER BY REVIEW_DATE, REVIEW_TEXT;

3. 리뷰를 가장 많이 작성한 회원이 여러 명인 경우 고려

-- 3. 리뷰를 가장 많이 작성한 회원의 리뷰 조회
SELECT A.MEMBER_NAME, B.REVIEW_TEXT, 
DATE_FORMAT(B.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE AS A
JOIN REST_REVIEW AS B
ON A.MEMBER_ID = B.MEMBER_ID
WHERE A.MEMBER_ID IN (
    -- 2. 리뷰를 가장 많이 작성한 회원ID 조회
    SELECT MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    HAVING COUNT(*) = (
        -- 1. 한 회원이 작성한 리뷰수의 최댓값 조회
        SELECT MAX(A.COUNT) AS MAX_COUNT
        FROM (
            SELECT COUNT(*) AS COUNT
            FROM REST_REVIEW
            GROUP BY MEMBER_ID
        ) AS A
    )
)
ORDER BY REVIEW_DATE, REVIEW_TEXT;

없어진 기록 찾기(Lv. 3)

• 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물 → AO - AI
• ON, WHERE 절 조건을 잘 쓰는 것이 중요하다!

1. LEFT JOIN

SELECT AO.ANIMAL_ID, AO.NAME
FROM ANIMAL_OUTS AS AO
LEFT JOIN ANIMAL_INS AS AI
ON AO.ANIMAL_ID = AI.ANIMAL_ID
WHERE AI.ANIMAL_ID IS NULL
ORDER BY AO.ANIMAL_ID, AO.NAME;

1. RIGHT JOIN

SELECT AO.ANIMAL_ID, AO.NAME
FROM ANIMAL_INS AS AI
RIGHT JOIN ANIMAL_OUTS AS AO
ON AI.ANIMAL_ID = AO.ANIMAL_ID
WHERE AI.ANIMAL_ID IS NULL
ORDER BY AO.ANIMAL_ID, AO.NAME;

있었는데요 없었습니다(Lv. 3)

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS AS A
JOIN ANIMAL_OUTS AS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.DATETIME > B.DATETIME
ORDER BY A.DATETIME;

• 보호 시작일보다 입양일이 더 빠른 동물 → A JOIN B(A ∩ B)

오랜 기간 보호한 동물(1) (Lv. 3)

SELECT A.NAME, A.DATETIME
FROM ANIMAL_INS AS A
LEFT JOIN ANIMAL_OUTS AS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL
ORDER BY DATETIME
LIMIT 3;

• 아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 → A LEFT JOIN B(A - B)

보호소에서 중성화한 동물(Lv. 4)

• “보호소를 나갈 당시에는 중성화된” 에 대한 쿼리를 짤 때 2가지 방식이 있음

1. NOT LIKE

SELECT AI.ANIMAL_ID, AI.ANIMAL_TYPE, AI.NAME
FROM ANIMAL_INS AS AI
JOIN ANIMAL_OUTS AS AO
ON AI.ANIMAL_ID = AO.ANIMAL_ID
WHERE AI.SEX_UPON_INTAKE LIKE 'Intact%'
AND AO.SEX_UPON_OUTCOME NOT LIKE 'Intact%'
ORDER BY AI.ANIMAL_ID;

• 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물 → AI JOIN AO(AI ∩ AO)

1. LIKE, OR

SELECT AI.ANIMAL_ID, AI.ANIMAL_TYPE, AI.NAME
FROM ANIMAL_INS AS AI
JOIN ANIMAL_OUTS AS AO
ON AI.ANIMAL_ID = AO.ANIMAL_ID
WHERE AI.SEX_UPON_INTAKE LIKE 'Intact%'
AND (AO.SEX_UPON_OUTCOME LIKE 'Spayed%'
     OR
     AO.SEX_UPON_OUTCOME LIKE 'Neutered%')
ORDER BY AI.ANIMAL_ID;

상품 별 오프라인 매출 구하기(Lv. 2)

SELECT B.PRODUCT_CODE, SUM(B.PRICE * A.SALES_AMOUNT) AS SALES
FROM OFFLINE_SALE AS A
JOIN PRODUCT AS B
ON A.PRODUCT_ID = B.PRODUCT_ID
GROUP BY B.PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE;

• 상품별 → GROUP BY
• 매출액(판매가 * 판매량) 합계 → SUM(B.PRICE * A.SALES_AMOUNT)

상품을 구매한 회원 비율 구하기(Lv. 5)

2021년에 가입한 회원수를 구해서 사용하는 방식에 2가지가 있다.

1. 서브 쿼리를 바로 사용하기

SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH,
COUNT(DISTINCT OS.USER_ID) AS PURCHASED_USERS,
ROUND(COUNT(DISTINCT OS.USER_ID) / (SELECT COUNT(*)
                         FROM USER_INFO
                         WHERE YEAR(JOINED) = 2021), 1) AS PURCHASED_RATIO
FROM USER_INFO AS UI
JOIN ONLINE_SALE AS OS
ON UI.USER_ID = OS.USER_ID
WHERE YEAR(UI.JOINED) = 2021
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;

• 상품을 구매한 회원수 → COUNT(DISTINCT OS.USER_ID)
  • 어떤 회원이 해당 년, 월에 상품을 구매한 이력이 2회 이상일 수 있기 때문에 DISTINCT 로 중복을 걸러주어야 한다.

1. 서브쿼리를 WITH 절로 기술해두고 사용하기

-- 1. 2021년에 가입한 전체 회원수
WITH TMP AS(
    SELECT COUNT(USER_ID) AS USERS
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
)

SELECT YEAR(A.SALES_DATE) AS YEAR, MONTH(A.SALES_DATE) AS MONTH, 
-- 년,월 별로 2021년에 가입한 회원 중 상품을 구매한 회원수
COUNT(DISTINCT A.USER_ID) AS PURCHASED_USERS,
-- 년,월 별로 2021년에 가입한 회원 중 상품을 구매한 회원의 비율
ROUND(COUNT(DISTINCT A.USER_ID) / (TMP.USERS), 1) AS PURCHASED_RATIO
FROM TMP,
ONLINE_SALE AS A
JOIN USER_INFO AS B
ON A.USER_ID = B.USER_ID
WHERE YEAR(B.JOINED) = 2021
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;

• FROM 절 테이블 , : INNER JOIN 과 같은 역할

1. WITH 절 2개

WITH TMP AS(
    -- 2021년에 가입한 전체 회원 수
    SELECT COUNT(*) AS CNT
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
),
TMP2 AS(
    -- 2021년에 가입한 전체 회원 정보
    SELECT *
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
)

SELECT YEAR(A.SALES_DATE) AS YEAR, MONTH(A.SALES_DATE) AS MONTH,
COUNT(DISTINCT B.USER_ID) AS PURCHASED_USERS,
ROUND(COUNT(DISTINCT B.USER_ID) / C.CNT, 1) AS PURCHASED_RATIO
FROM ONLINE_SALE AS A
JOIN TMP2 AS B
ON A.USER_ID = B.USER_ID
, TMP AS C
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;

특정 기간동안 대여 가능한 자동차들의 대여비용 구하기(Lv. 4)

1번 풀이가 더 정석적인 풀이인 것 같다.

1. 서브쿼리, JOIN, GROUP BY

SELECT A.CAR_ID, A.CAR_TYPE,
FLOOR(A.DAILY_FEE * 30 * (1 - C.DISCOUNT_RATE / 100)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS A
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS C
ON A.CAR_TYPE = C.CAR_TYPE
WHERE A.CAR_TYPE IN ('세단', 'SUV')
AND C.DURATION_TYPE = '30일 이상'
AND A.CAR_ID NOT IN (
    -- 2022-11-01 ~ 2022-11-30 기간에 대여한 이력이 있는 차
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE (START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01')
    GROUP BY CAR_ID
)
GROUP BY A.CAR_ID
HAVING 500000 <= FEE AND FEE < 2000000
ORDER BY FEE DESC, A.CAR_TYPE ASC, A.CAR_ID DESC;

1. 서브쿼리

• CAR_TYPE, DURATION_TYPE 별 할인율을 미리 조회하기

# SELECT * 
# FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
# WHERE CAR_TYPE IN ('세단', 'SUV')
# AND DURATION_TYPE = '30일 이상';

SELECT A.CAR_ID, A.CAR_TYPE, 
FLOOR(30 * A.DAILY_FEE * IF(CAR_TYPE = '세단', 0.92, 0.95)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS A
WHERE A.CAR_ID NOT IN(
    -- 2022-11-01 ~ 2022-11-30 기간에 대여한 이력이 있는 차
    SELECT DISTINCT A.CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS A
    WHERE NOT (A.END_DATE < '2022-11-01' OR '2022-11-30' < A.START_DATE)
)
AND CAR_TYPE IN ('세단', 'SUV')
AND 500000 <= FLOOR(30 * A.DAILY_FEE * IF(CAR_TYPE = '세단', 0.92, 0.95))
AND FLOOR(30 * A.DAILY_FEE * IF(CAR_TYPE = '세단', 0.92, 0.95)) < 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC;

-- 1. 세단 또는 SUV인 자동차의 ID, 종류 30일간의 대여금액
WITH TMP AS (
    SELECT CAR_ID, CAR_TYPE,
    FLOOR(30 * DAILY_FEE * 
        CASE
            WHEN CAR_TYPE = '세단' THEN 0.92
            WHEN CAR_TYPE = 'SUV' THEN 0.95
        END) AS FEE
    FROM CAR_RENTAL_COMPANY_CAR
    WHERE CAR_TYPE IN ('세단', 'SUV')
)

SELECT *
FROM TMP
WHERE CAR_ID NOT IN (
        -- 2. 2022년 11월 1일부터 2022년 11월 30일까지 대여가 불가한 자동차 ID
    SELECT DISTINCT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE END_DATE >= '2022-11-01' 
    AND START_DATE <= '2022-11-30'
)
AND FEE BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;