프로그래머스 SQL 고득점 Kit - JOIN

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

SELECT F.FLAVOR
FROM FIRST_HALF F
LEFT JOIN (
    SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
    FROM JULY
    GROUP BY FLAVOR
) AS J ON F.FLAVOR = J.FLAVOR
ORDER BY (F.TOTAL_ORDER+IFNULL(J.TOTAL_ORDER,0)) DESC
LIMIT 3

// 7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문

 

- JULY 테이블에서는 FLAVOR 외래키에 대해 그룹화를 해주어야 하며

- 7월에 안팔렸지만 상반기에서는 팔린 맛이 있을 수 있기에 IFNULL 처리를 해주었음

 

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

SELECT A.CAR_ID, A.CAR_TYPE, FLOOR(A.DAILY_FEE * 30 * (100 - B.DISCOUNT_RATE) / 100) AS FEE
FROM CAR_RENTAL_COMPANY_CAR A
JOIN (
    -- 30일 대여 시 할인율
    SELECT CAR_TYPE, DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE REGEXP_REPLACE(DURATION_TYPE, '[^0-9]+', '') = 30
) B ON A.CAR_TYPE = B.CAR_TYPE
WHERE A.CAR_ID NOT IN (
    -- 2022-11 대여기록 한번이라도 있으면 제외
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01'
)
AND A.CAR_TYPE IN ('세단', 'SUV')
AND FLOOR(A.DAILY_FEE * 30 * (100 - B.DISCOUNT_RATE) / 100) >= 500000
AND FLOOR(A.DAILY_FEE * 30 * (100 - B.DISCOUNT_RATE) / 100) < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC

// 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문

 

- REGEXP_REPLACE 함수를 이용해 '' 문자로 REPLACE를 함으로써 문자열에서 숫자 혹은 문자만 출력할 수 있음

ex) REGEXP_REPLACE(숫자만 남길 문자열, '[^0-9]+', '') , REGEXP_REPLACE(문자만 남길 문자열, '[^0-9]+', '')

- 30일 대여 시 할인율을 INNER JOIN하고, 순서대로 대여기록 여부/차종/대여비용 순으로 필터링을 건다

- WHERE 절에서는 ALIAS(별칭)을 사용하지 못한다(테이블 별칭은 가능)!! GROUP BY나 ORDER BY는 가능

 

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

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

// FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문

 

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

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

// '경제' 카테고리에 속하는 도서들의 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문

 

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

SELECT A.MEMBER_NAME, C.REVIEW_TEXT, DATE_FORMAT(C.REVIEW_DATE, "%Y-%m-%d") AS DATE_FORMAT
FROM MEMBER_PROFILE A
JOIN (
    -- 회원별 리뷰 작성 수 구함
    SELECT MEMBER_ID, COUNT(REVIEW_ID) AS COUNT
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
) B ON A.MEMBER_ID = B.MEMBER_ID
JOIN REST_REVIEW C ON A.MEMBER_ID = C.MEMBER_ID
WHERE B.COUNT = (
    SELECT COUNT(REVIEW_ID) AS COUNT
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    ORDER BY COUNT DESC
    LIMIT 1
)
ORDER BY DATE_FORMAT ASC, C.REVIEW_TEXT ASC

// 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문

 

6. 없어진 기록 찾기

SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS O
LEFT JOIN ANIMAL_INS I ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY ANIMAL_ID ASC

// 천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문

 

- LEFT JOIN 하면 ANIMAL_OUTS에는 있고 ANIMAL_INS에는 없는 레코드를 구분할 수 있

 

7. 있었는데요 없었습니다

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

// 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문

 

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

SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS I
LEFT JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.ANIMAL_ID IS NULL
ORDER BY I.DATETIME ASC
LIMIT 3

// 아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문

 

9. 보호소에서 중성화한 동물

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 "Neutered%" OR O.SEX_UPON_OUTCOME LIKE "Spayed%")

// 보호소에 들어올 당시에는 중성화1되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문

 

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

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

// 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문

 

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

SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH, COUNT(DISTINCT USER_ID) AS PUCHASED_USERS, ROUND(COUNT(DISTINCT USER_ID)/(
    -- 21년에 가입한 회원 수
    SELECT COUNT(USER_ID)
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
), 1) AS PUCHASED_RATIO
FROM ONLINE_SALE
WHERE USER_ID IN (
    -- 21년에 가입한 회원
    SELECT USER_ID
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
)
GROUP BY YEAR, MONTH
ORDER BY YEAR ASC, MONTH ASC

// 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문

 

- 21년에 가입한 회원 ID 리스트와 회원 수를 서브쿼리로 구함

- YEAR과 MONTH를 기준으로 그룹화하고, USER_ID 수를 COUNT한다. 이 때 한 회원이 여러번 구매했을 수 있으므로 DISTINCT 적용이 필수이다!!

- USER_ID 기준으로 INNER JOIN 시켜서 풀어도 되겠음