[SQL / 프로그래머스] JOIN 문제 모음

 

 

https://school.programmers.co.kr/learn/courses/30/lessons/131117

프로그래머스

 

우선 5월에 만들어진 상품들의 총 주문 개수를 구해야한다고 생각했습니다.

해당 쿼리는 아래와 같습니다.

SELECT PRODUCT_ID, SUM(AMOUNT)
FROM FOOD_ORDER
WHERE PRODUCE_DATE >= '2022-05-01' AND PRODUCE_DATE <= '2022-05-31'
GROUP BY PRODUCT_ID;

 

그리고 저 AMOUNT의 합과 가격을 곱해주면 된다고 생각해서 JOIN을 활용해서 정답쿼리를 썼습니다.

 

SELECT FP.PRODUCT_ID AS PRODUCT_ID, FP.PRODUCT_NAME AS PRODUCT_NAME, SUM(FO.AMOUNT)* FP.PRICE AS TOTAL_SALES
FROM FOOD_PRODUCT AS FP
JOIN FOOD_ORDER AS FO ON FO.PRODUCT_ID = FP.PRODUCT_ID
WHERE FO.PRODUCE_DATE >= '2022-05-01' AND FO.PRODUCE_DATE <= '2022-05-31'
GROUP BY PRODUCT_ID
ORDER BY TOTAL_SALES DESC, PRODUCT_ID;

 

 

---

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

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

https://school.programmers.co.kr/learn/courses/30/lessons/133027

프로그래머스

 

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

 

 

SELECT 절에 집계합수가 없어도 GROUP BY가 되는구나

 

 

---

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

https://school.programmers.co.kr/learn/courses/30/lessons/157339

프로그래머스

 

우선 할인율을 구하는 쿼리는 아래와 같다. 이를 FROM 절에 조인 절 안에다 넣어줄 것이다.

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

 

그리고 2022-11-01, 2022-11-30 사이에 대여기록이 있는 차는 빌릴 수 없다.

빌릴수 없는 CAR_ID들을 구해주자.

이를 WHERE절에서 NOT IN 과 함께 사용해주려고 한다.

SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CH2
WHERE (CH2.START_DATE <= '2022-11-01' AND CH2.END_DATE >= '2022-11-30')
OR (CH2.START_DATE >= '2022-11-01' AND CH2.START_DATE <= '2022-11-30')
OR (CH2.END_DATE >= '2022-11-01' AND CH2.END_DATE <= '2022-11-30')

 

 

전체 쿼리

SELECT CC.CAR_ID AS CAR_ID, CC.CAR_TYPE AS CAR_TYPE, CONVERT(30 * CC.DAILY_FEE * ((100-CD.DISCOUNT_RATE) / 100), SIGNED) AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CH
JOIN CAR_RENTAL_COMPANY_CAR AS CC ON CC.CAR_ID = CH.CAR_ID
JOIN (SELECT * FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS CP WHERE DURATION_TYPE = '30일 이상' AND CAR_TYPE IN ('세단', 'SUV')) AS CD ON CD.CAR_TYPE = CC.CAR_TYPE
WHERE CC.CAR_ID NOT IN (
    SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CH2
    WHERE (CH2.START_DATE <= '2022-11-01' AND CH2.END_DATE >= '2022-11-30')
    OR (CH2.START_DATE >= '2022-11-01' AND CH2.START_DATE <= '2022-11-30')
    OR (CH2.END_DATE >= '2022-11-01' AND CH2.END_DATE <= '2022-11-30')
)
AND CC.CAR_TYPE IN ('세단', 'SUV')
GROUP BY CC.CAR_ID
HAVING FEE >= 500000 AND FEE <= 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC;

 

 

이걸 쿼리가 좀더 깔끔해지게 WITH 절을 이용한 풀이도 있다.

WITH CANT_RENT AS (
    SELECT * FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE (START_DATE <= '2022-11-01' AND END_DATE >= '2022-11-30')
    OR (START_DATE >= '2022-11-01' AND START_DATE <= '2022-11-30')
    OR (END_DATE >= '2022-11-01' AND END_DATE <= '2022-11-30')
),

DISCOUNT_POLICY AS (
    SELECT * FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE DURATION_TYPE = '30일 이상'
)
SELECT CC.CAR_ID AS CAR_ID, CC.CAR_TYPE AS CAR_TYPE,
CONVERT(CC.DAILY_FEE * 30 * (100 - DP.DISCOUNT_RATE) / 100, SIGNED) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS CC
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CH ON CH.CAR_ID = CC.CAR_ID
JOIN DISCOUNT_POLICY AS DP ON DP.CAR_TYPE = CC.CAR_TYPE
WHERE CC.CAR_ID NOT IN (SELECT CANT_RENT.CAR_ID FROM CANT_RENT)
GROUP BY CAR_ID
HAVING FEE >= 500000 AND FEE <= 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC;