lv4) 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 [틀림, 다시]

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

프로그래머스

 

오답에 따른 답지 참조

SELECT 
    C.CAR_ID, 
    C.CAR_TYPE, 
    FLOOR(C.DAILY_FEE * 30 * (100 - 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 P.DURATION_TYPE = '30일 이상'          -- ← 30일 대여 할인율
WHERE C.CAR_TYPE IN ('세단', 'SUV')
  AND C.CAR_ID NOT IN (
      SELECT CAR_ID
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
      WHERE START_DATE <= '2022-11-30'         -- ← 기간 겹침 조건
        AND END_DATE >= '2022-11-01'
  )
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, C.CAR_ID DESC;

 

 

다시하기

 

오답 코드

-- 코드를 입력하세요
# Rental_company_car에서 suv , 세단 선택, 히스토리테이블 조인해서
# 히스토리테이블에 없거나, 있어도 start_date ~ end_date에 end_date가 11월 1일 이전 또는 start_Date11월 30 이후 인 날짜만 선택
# 30일 간 대여하니, DAILYFEE * 30 * 기간 타입에 따른 DISCOUNT_RATE  곱하면 됨

SELECT CH.CAR_ID, CH.CAR_TYPE, DAILY_FEE * 30 * ((100-P.DISCOUNT_RATE)/100) AS FEE FROM 
(SELECT C.CAR_ID, C.CAR_TYPE, C.DAILY_FEE, H.START_DATE, H.END_DATE from CAR_RENTAL_COMPANY_CAR AS C
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
ON C.CAR_ID = H.CAR_ID
WHERE C.CAR_ID NOT IN( SELECT C.CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                    WHERE START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01')
 AND (CAR_TYPE = '세단' OR CAR_TYPE = 'SUV')
) AS CH
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON CH.CAR_TYPE = P.CAR_TYPE
GROUP BY CAR_ID
ORDER BY FEE DESC, CAR_TYPE ASC, CH.CAR_ID DESC;