[0121 TIL] sql 코드카타 74 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기(3개 Join, having min/max)

 

문제

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

SELECT C.CAR_ID, C.CAR_TYPE, ROUND(C.DAILY_FEE*30*(1-P.DISCOUNT_RATE/100)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID = H.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON C.CAR_TYPE = P.CAR_TYPE 
AND P.DURATION_TYPE = '30일 이상'
WHERE C.CAR_TYPE IN ('SUV', '세단')
GROUP BY C.CAR_ID, C.CAR_TYPE
HAVING MIN(H.START_DATE) > '2022-11-30' OR MAX(H.END_DATE) < '2022-11-01' 
AND FEE BETWEEN 500000 AND 2000000

 

날짜 거르는 것에 있어서 말이 너무 헷갈렸고 역대급으로 오래걸림

처음에는 세 데이터 다 join 한 후 where 로 거르려고 했다가 실패

join에서 서브쿼리에 다 넣고 그 안에서 where거르고 join 하려다가 실패

내가보기엔 다 날짜 거르다가 그렇게 된 듯 날짜를 거르는 방법에서 다양해서 아래와 같이 여러 풀이과정이 나온 것 같은데 나는 그나마 가장 직관적이고 쉬운 것으로 풀었다.

30일 이상 대여 가능하고 11월에 대여 가능해야하니까 한 id의 여러 date중 start의 min, end의 max 를 11월 바깥으로 설정해서 걸렀다.

 

새롭게 배운 것 보다는 원래 배운것들의 조합이었던 것 같은데 너무 어려웠음..!

 

 

다른 정답풀이

SELECT 
    C.CAR_ID
    , C.CAR_TYPE
    , ROUND(C.DAILY_FEE * 30 * (1 - P.DISCOUNT_RATE/100)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
    ON C.CAR_TYPE = P.CAR_TYPE
    AND C.CAR_TYPE IN ('SUV', '세단')
    AND P.DURATION_TYPE = '30일 이상'
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
    ON C.CAR_ID = H.CAR_ID
    AND H.START_DATE <= '2022-11-30'
    AND H.END_DATE >= '2022-11-01'
WHERE H.START_DATE IS NULL
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC

https://susinlee.tistory.com/124

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

 

SELECT DISTINCT C.CAR_ID, C.CAR_TYPE, FLOOR(30*DAILY_FEE*(1 - DISCOUNT_RATE/100)) AS FEE
    FROM CAR_RENTAL_COMPANY_CAR AS C
    JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
      ON C.CAR_ID = H.CAR_ID 
    JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS D
      ON C.CAR_TYPE = D.CAR_TYPE AND
         D.DURATION_TYPE = '30일 이상'
    WHERE C.CAR_TYPE IN ('세단', 'SUV') 
          AND C.CAR_ID NOT IN (SELECT CAR_ID 
                                 FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                                WHERE NOT (END_DATE < '2022-11-01' OR 
                                           '2022-11-30' < START_DATE))
    HAVING FEE BETWEEN 500000 AND 2000000-1
    ORDER BY 3 DESC, 2, 1 DESC;

https://ror-coding.tistory.com/134

[Programmers Lv.4] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 - 157339

 

-- 2022년 11월 1일부터 2022년 11월 30일까지 대여 불가능한 차량 ID 
WITH CANT_RENTAL AS (
    SELECT DISTINCT CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE 
    DATE_FORMAT(START_DATE, '%Y-%m') = '2022-11' OR 
    DATE_FORMAT(END_DATE, '%Y-%m') = '2022-11' OR 
    (START_DATE < '2022-11-01' AND END_DATE >= '2022-12-01')
),
DISCOUNT AS (
    SELECT CAR_TYPE, DISCOUNT_RATE 
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE DURATION_TYPE	= '30일 이상'
)
SELECT C.CAR_ID, C.CAR_TYPE, 
CONVERT((C.DAILY_FEE * 30) * (100 - D.DISCOUNT_RATE) / 100 , UNSIGNED) FEE
FROM CAR_RENTAL_COMPANY_CAR C JOIN DISCOUNT D 
ON C.CAR_TYPE = D.CAR_TYPE 
WHERE C.CAR_ID NOT IN (SELECT * FROM CANT_RENTAL)
HAVING FEE BETWEEN 500000 AND 1999999
ORDER BY 3 DESC, 2 ASC , 1 DESC
출처: https://20240228.tistory.com/262 [지적 겸손함:티스토리]