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

제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
Programmers SQL 고득점 Kit(JOIN)입니다.

특정 기간동안 대여 가능한 자동차들의 대여 비용 구하기

SELECT
    H.CAR_ID,
    C.CAR_TYPE,
    C.DAILY_FEE * 30 * (1 - (DISCOUNT_RATE / 100)) AS FEE
FROM (
    SELECT DISTINCT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
    GROUP BY CAR_ID
    HAVING MAX(TO_CHAR(END_DATE, 'YYYY-MM')) < '2022-11'
) H JOIN (
    SELECT CAR_ID, CAR_TYPE, DAILY_FEE
    FROM CAR_RENTAL_COMPANY_CAR C
    WHERE CAR_TYPE = '세단' OR CAR_TYPE = 'SUV'
) C ON H.CAR_ID = C.CAR_ID JOIN (
    SELECT CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE (CAR_TYPE = '세단' OR CAR_TYPE = 'SUV') AND (DURATION_TYPE = '30일 이상')
) P ON C.CAR_TYPE = P.CAR_TYPE
WHERE C.DAILY_FEE * 30 * (1 - (DISCOUNT_RATE / 100)) BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, C.CAR_TYPE ASC, H.CAR_ID DESC

• 2022년 11월 대여가 가능한 차를 찾아야 하기에 CAR_RENTAL_COMPANY_RENTAL_HISTORY에서 반납일이 11월보다 작은 차를 조회하기 위한 서브 테이블 H를 생성합니다.

(
    SELECT DISTINCT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
    GROUP BY CAR_ID
    HAVING MAX(TO_CHAR(END_DATE, 'YYYY-MM')) < '2022-11'
) H

• 자동차의 종류가 세단이나 SUV여야 하므로 서브테이블 C를 생성합니다.

(
    SELECT CAR_ID, CAR_TYPE, DAILY_FEE
    FROM CAR_RENTAL_COMPANY_CAR C
    WHERE CAR_TYPE = '세단' OR CAR_TYPE = 'SUV'
) C

• H테이블과 C 테이블을 CAR_ID를 기준으로 조인합니다
  FROM (...) H JOIN (...)C ON H.CAR_ID = C.CAR_ID
• 세단과 SUV의 30일 이상 대여할 시의 할인율을 구하기 위한 서브 테이블 P를 생성합니다.

(
    SELECT CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE (CAR_TYPE = '세단' OR CAR_TYPE = 'SUV') AND (DURATION_TYPE = '30일 이상')
) P

• P 테이블과 C테이블의 CAR_TYPE을 기준으로 조인합니다.
  C.CAR_TYPE = P.CAR_TYPE
• 30일 간의 대여 금액이 50만원 이상 200만원 미만인 자동차를 구하기 위해 BETWEEN 함수를 이용하빈다.
  DISCOUNT_RATE가 퍼센트로 표시되어있기에 해당 계산을 이용합니다.
  WHERE C.DAILY_FEE * 30 * (1 - (DISCOUNT_RATE / 100)) BETWEEN 500000 AND 2000000
• 자동차 종류를 기준으로 오름차순 정렬, 자동차 ID를 기준으로 내림차순 정렬합니다.

5월 식품들의 총 매출 조회하기

SELECT
    P.PRODUCT_ID,
    P.PRODUCT_NAME,
    P.PRICE * O.AMOUNT AS TOTAL_SALES
FROM FOOD_PRODUCT P JOIN (
    SELECT PRODUCT_ID, SUM(AMOUNT) AS AMOUNT
    FROM FOOD_ORDER O
    WHERE TO_CHAR(PRODUCE_DATE, 'YYYY-MM') = '2022-05'
    GROUP BY PRODUCT_ID
) O ON P.PRODUCT_ID = O.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID ASC

• 2022년 5월에 생산된 식품을 조회하기 위한 서브 테이블 O를 생성합니다.
  총 매출(TOTAL_ORDER * PRICE)을 구해야하기 때문에 식품ID를 기준으로 그룹화를 하고 SUM 함수를 사용합니다.

(
    SELECT PRODUCT_ID, SUM(AMOUNT) AS AMOUNT
    FROM FOOD_ORDER O
    WHERE TO_CHAR(PRODUCE_DATE, 'YYYY-MM') = '2022-05'
    GROUP BY PRODUCT_ID
) O

 

• FOOD_PRODUCT 테이블과 PRODUCT_ID를 기준으로 JOIN합니다.
  FROM FOOD_PRODUCT P JOIN (...) O ON P.PRODUCT_ID = O.PRODUCT_ID
• 총 매출을 구하기 위해 다음과 같이 TOTAL_SALES 컬럼을 구성합니다.
  P.PRICE * O.AMOUNT AS TOTAL_SALES
• 총 매출을 기준으로 내림차순 정렬하고, 식품 ID를 기준으로 오름차순 정렬합니다.

주문량이 많은 아이스크림들 조회하기

SELECT
    H.FLAVOR
FROM (
    SELECT FLAVOR, TOTAL_ORDER
    FROM FIRST_HALF
) H JOIN (
    SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
    FROM JULY
    GROUP BY FLAVOR
) J ON H.FLAVOR = J.FLAVOR
ORDER BY H.TOTAL_ORDER + J.TOTAL_ORDER DESC
FETCH NEXT 3 ROWS ONLY

• 상반기 아이스크림의 총 주문량을 구하기 위한 서브 테이블 H를 생성합니다.

(
    SELECT FLAVOR, TOTAL_ORDER
    FROM FIRST_HALF
) H

• 7월에는 주문량이 많아 다른 두 공장에서 같은 맛을 출하를 진행하였으므로 FLAVOR를 기준으로 그룹화를 진행하고 총 주문량을 조회하는 서브테이르 J를 생성합니다.

(
    SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
    FROM JULY
    GROUP BY FLAVOR
) J

• H 테이블과 J 테이블을 FLAVOR을 기준으로 JOIN 합니다.
  FROM (...) H JOIN (...) J ON H.FLAVOR = J.FLAVOR
• 총 주문량에 대해 내림차순 정렬 후 상위 3개의 레코드를 조회합니다.
  ORDER BY H.TOTAL_ORDER + J.TOTAL_ORDER DESC
  FETCH NEXT 3 ROWS ONLY

조건에 맞는 도서와 저자 리스트 출력하기

SELECT 
    B.BOOK_ID, 
    A.AUTHOR_NAME, 
    TO_CHAR(B.PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISHED_DATE
FROM BOOK B JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE B.CATEGORY = '경제'
ORDER BY B.PUBLISHED_DATE

• 도서 테이블과 저자 테이블을 JOIN하기 위해 AUTHOR_ID를 기준으로 JOIN합니다.
  FROM BOOK B JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
• 경제 카테고리에 해당하는 도서를 찾기 위해 조건을 걸어줍니다.
  WHERE B.CATEGORY = '경제'
• 출판일을 기준으로 오름차순 정렬합니다.

그룹별 조건에 맞는 식당 목록 출력하기

WITH MemberReviewCount AS (
    SELECT
        MEMBER_ID,
        COUNT(*) AS REVIEW_COUNT
    FROM
        REST_REVIEW
    GROUP BY
        MEMBER_ID
),
MaxReviewMember AS (
    SELECT
        MEMBER_ID
    FROM
        MemberReviewCount
    WHERE
        REVIEW_COUNT = (SELECT MAX(REVIEW_COUNT) FROM MemberReviewCount)
)

SELECT
    MP.MEMBER_NAME,
    RR.REVIEW_TEXT,
    TO_CHAR(RR.REVIEW_DATE, 'YYYY-MM-DD')
FROM
    REST_REVIEW RR
    JOIN MEMBER_PROFILE MP ON RR.MEMBER_ID = MP.MEMBER_ID
    JOIN MaxReviewMember MRM ON RR.MEMBER_ID = MRM.MEMBER_ID
ORDER BY
    TO_CHAR(RR.REVIEW_DATE, 'YYYY-MM-DD') ASC,
    RR.REVIEW_TEXT ASC;

• WITH 키워드를 이용하여 테이블을 미리 생성합니다.
  첫번째 테이블 MemberReviewCount는 MEMBER_ID를 기준으로 리뷰 횟수를 조회하는 테이블을 생성합니다.

WITH MemberReviewCount AS (
    SELECT
        MEMBER_ID,
        COUNT(*) AS REVIEW_COUNT
    FROM
        REST_REVIEW
    GROUP BY
        MEMBER_ID
),

• 두번째 테이블은 리뷰를 가장 많이 작성한 회원 ID를 조회하기 위한 테이블을 생성합니다

MaxReviewMember AS (
    SELECT
        MEMBER_ID
    FROM
        MemberReviewCount
    WHERE
        REVIEW_COUNT = (SELECT MAX(REVIEW_COUNT) FROM MemberReviewCount)
)

• 레스토랑 리뷰 테이블과 멤버 정보 테이블, MaxReviewMember 테이블을 Member_ID를 기준으로 JOIN 합니다.
  FROM
      REST_REVIEW RR
      JOIN MEMBER_PROFILE MP ON RR.MEMBER_ID = MP.MEMBER_ID
      JOIN MaxReviewMember MRM ON RR.MEMBER_ID = MRM.MEMBER_ID
• 리뷰 작성일을 기준으로 오름차순, 리뷰 텍스트를 기준으로 오름차순 정렬합니다.

없어진 기록 찾기

SELECT
    O.ANIMAL_ID,
    O.NAME
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID(+) = O.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID, O.NAME;

• 동물 보호소에 들어온 동물과 입양 보낸 동물들을 ANIMAL_ID를 기준으로 LEFT JOIN합니다.
  LEFT JOIN하는 이유는 입양 들어온 기록은 존재하는데 입양 보낸 기록이 없을 때를 찾기 위함입니다.
  FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID(+) = O.ANIMAL_ID
• 없어진 동물의 기록을 찾아야하기 때문에 I.ANIMAL_ID가 NULL인 데이터를 조회합니다.
  WHERE I.ANIMAL_ID IS NULL
• 동물 ID와 이름을 기준으로 오름차순 정렬합니다.

있었는데요 없었습니다

SELECT
    I.ANIMAL_ID,
    I.NAME
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.DATETIME > O.DATETIME
ORDER BY I.DATETIME ASC

• 동물 보호소에 들어온 동물과 입양 보낸 동물들을 ANIMAL_ID를 기준으로 JOIN합니다.
  FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
• 보호 시작일이 입양일보다 더 늦은 레코드를 조회하기 위해 조건을 걸어줍니다.
  WHERE I.DATETIME > O.DATETIME
• 보호 시작일이 빠른 순으로 조회합니다.(오름차순으로 정렬합니다)

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

SELECT
    I.NAME, 
    I.DATETIME
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID(+)
WHERE O.ANIMAL_ID IS NULL
ORDER BY I.DATETIME ASC
FETCH NEXT 3 ROWS ONLY

• 동물 보호소에 들어온 동물과 입양 보낸 동물들을 ANIMAL_ID를 기준으로 RIGHT JOIN합니다.
  RIGHT JOIN하는 이유는 입양을 아직 못간 동물을 찾기 위합니다.
  FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID(+)
• 입양을 못간 동물을 조회하기 위해 조건을 걸어줍니다.
  WHERE O.ANIMAL_ID IS NULL
• 보호 시작일을 기준으로 오름차순으로 정렬하고 상위 3개의 레코드를 조회하기 위해 FETCH NEXT 절을 사용합니다.
  ORDER BY I.DATETIME ASC
  FETCH NEXT 3 ROWS ONLY

보호소에서 중성화한 동물

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

• 동물 보호소에 들어온 동물과 입양 보낸 동물들을 ANIMAL_ID를 기준으로 JOIN합니다.
  FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
• 보호소에서 중성화한 동물을 찾기 위해 조건을 걸어줍니다.
  들어왔을 때는 아니였지만 나갈때 중성화가 되어있으면 보호소에서 중성화 한 것입니다.
  WHERE I.SEX_UPON_INTAKE LIKE 'Intact%' 
      AND (O.SEX_UPON_OUTCOME LIKE 'Spayed%' OR O.SEX_UPON_OUTCOME LIKE 'Neutered%')
• 동물 ID를 기준으로 오름차순 정렬합니다.

상품 별 오프라인 매출 구하기

SELECT
    P.PRODUCT_CODE,
    P.PRICE * S.TOTAL_SALES AS SALES
FROM PRODUCT P JOIN (
    SELECT PRODUCT_ID, SUM(SALES_AMOUNT) AS TOTAL_SALES
    FROM OFFLINE_SALE
    GROUP BY PRODUCT_ID
) S ON P.PRODUCT_ID = S.PRODUCT_ID
ORDER BY SALES DESC, P.PRODUCT_CODE ASC

• 오프라인에서 상품별 판매량을 구하기 위해 상품 ID를 기준으로 그룹화한 테이블을 S로 생성합니다.

(
    SELECT PRODUCT_ID, SUM(SALES_AMOUNT) AS TOTAL_SALES
    FROM OFFLINE_SALE
    GROUP BY PRODUCT_ID
) S

• 상품 테이블과 S를 PRODUCT_ID를 기준으로 JOIN합니다.
  FROM PRODUCT P JOIN (...) S ON P.PRODUCT_ID = S.PRODUCT_ID
• 매출을 구하기 위해 다음과 같이 필드를 생성합니다.
  P.PRICE * S.TOTAL_SALES AS SALES
• 총 매출량을 기준으로 내림차순, 상품 코드를 기준으로 오름차순 정렬합니다.

상품을 구매한 회원 비율 구하기

SELECT 
    TO_CHAR(O.SALES_DATE, 'YYYY') AS YEAR,
    EXTRACT(MONTH FROM O.SALES_DATE) AS MONTH,
    COUNT(DISTINCT U.USER_ID) AS PUCHASED_USERS,
    ROUND(COUNT(DISTINCT U.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE TO_CHAR(joined, 'YYYY') = '2021'), 1) AS PUCHASED_RATIO
FROM USER_INFO U JOIN ONLINE_SALE O ON U.USER_ID = O.USER_ID
WHERE TO_CHAR(U.JOINED, 'YYYY') = '2021'
GROUP BY TO_CHAR(O.SALES_DATE, 'YYYY'), EXTRACT(MONTH FROM O.SALES_DATE)
ORDER BY TO_CHAR(O.SALES_DATE, 'YYYY'), EXTRACT(MONTH FROM O.SALES_DATE)

• 상품을 구매한 회원을 찾기 위해 USER_INFO 테이블과 ONLINE_SALE 테이블을 JOIN 합니다.
  FROM USER_INFO U JOIN ONLINE_SALE O ON U.USER_ID = O.USER_ID
• 그 중 2021년에 가입한 회원을 구하기 위해 조건을 걸어줍니다.
  WHERE TO_CHAR(U.JOINED, 'YYYY') = '2021'
• 연, 월별로 (구매 회원수 / 가입 회원) 을 구해야하기에 연, 월을 기준으로 그룹화를 진행합니다.
  GROUP BY TO_CHAR(O.SALES_DATE, 'YYYY'), EXTRACT(MONTH FROM O.SALES_DATE)
• 상품을 구매한 회원의 비율을 소수점 두번째 자리에서 반올림해야 하기에 다음과 같이 컬럼을 구성합니다.
  2021년에 가입한 회원의 전체 수를 구하기 위해 서브 쿼리를 이용합니다.
  ROUND(COUNT(DISTINCT U.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE TO_CHAR(joined, 'YYYY') = '2021'), 1) AS PUCHASED_RATIO
• 연과 월을 기준으로 오름차순 정렬합니다.

FrontEnd 개발자 찾기

SELECT
    D.ID,
    D.EMAIL,
    D.FIRST_NAME,
    D.LAST_NAME
FROM
    DEVELOPERS D
WHERE
    EXISTS (
        SELECT 1
        FROM SKILLCODES S
        WHERE S.CATEGORY = 'FRONT END'
          AND (D.SKILL_CODE & S.CODE) <> 0
    )
ORDER BY
    D.ID ASC;

• 서브 쿼리를 사용합니다.
  SKILL이 FRONT END이고 DEVELOPERS의 SKILL_CODE와 비트 연산을 통해 0보다 큰(공통 비트가 존재하는) 컬럼을 조회합니다.

FROM
    DEVELOPERS D
WHERE
    EXISTS (
        SELECT 1
        FROM SKILLCODES S
        WHERE S.CATEGORY = 'FRONT END'
          AND (D.SKILL_CODE & S.CODE) <> 0
    )

• 개발자 ID를 기준으로 정렬합니다.