[SQL] 프로그래머스 Lv.4 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

74. 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를 기준으로 내림차순 정렬해주세요.

 

조건이 길고 복잡해보이는 문제지만 제시하는 조건을 정리하면 다음과 같다.

• 자동차 종류가 '세단' 또는 'SUV'인 자동차
• 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능
• 30일간의 대여 금액이 50만원 이상 200만원 미만

처음에는 3개의 테이블을 JOIN해주고 WHERE절에서 1, 2번 조건을 필터링해준 뒤 자동차 ID(car_id)로 그룹화 해주고 HAVING절에서 3번 조건을 필터링 후 정렬하여 쿼리문을 실행했다. 

-- 오답 코드

SELECT c.car_id, c.car_type, ROUND(c.daily_fee*(((100-p.discount_rate)*0.01))*30) FEE
FROM CAR_RENTAL_COMPANY_CAR c 
     JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h ON c.car_id=h.car_id
     JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN p ON c.car_type=p.car_type
WHERE c.car_type in ('세단', 'SUV') AND
      (h.start_date>'2022-11-30' OR h.end_date<'2022-11-01') AND
      p.duration_type='30일 이상'
GROUP BY 1
HAVING FEE BETWEEN 500000 AND 2000000
ORDER BY 3 DESC, 2, 1 DESC

 

모든 조건을 만족하도록 필터링 했다고 생각했는데 오답이 떠서 이유가 무엇일지 다른 사람들의 코드와 비교해보니 날짜 필터링에서 문제가 있는 것으로 파악됐다. 그룹화 해제 후 대여 시작일(start_date)과 대여 종료일(end_date) 컬럼도 같이 조회해봤을땐 문제가 없는 것처럼 보였는데 다시 생각해보니 해당 결과는 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능한 자동차가 아니라 대여 시작일이 2022년 11월 30일 이후이고 대여 종료일이 2022년 11월 1일 이전인 자동차만 출력된 것 이므로 만약 필터링되어 출력된 값 외에 해당 기간에 대여 중인 자동차가 있다면 오답으로 뜨는 것이었다.

 

직접 확인해 보기 위해서 출력된 자동차들의 모든 대여일을 조회해보았고 결과는 다음과 같다.

자동차 ID가 18, 27인 SUV에서 조건에 맞지 않는 대여일이 확인되었다.

 

날짜 조건을 다시 필터링하기 위해서 NOT IN 연산자를 사용하여 쿼리문을 작성했다.

먼저 2022년 11월 1일부터 2022년 11월 30일 사이에 대여중인 자동차 ID를 필터링하여 서브쿼리로 만들고 이에 해당하지 않는(NOT IN) 자동차 ID만 필터링 후 출력하였다.

SELECT c.car_id, c.car_type, ROUND(c.daily_fee*(((100-p.discount_rate)*0.01))*30) FEE
FROM CAR_RENTAL_COMPANY_CAR c 
     JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h ON c.car_id=h.car_id
     JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN p ON c.car_type=p.car_type
WHERE c.car_type IN ('세단', 'SUV') AND      
      p.duration_type='30일 이상' 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') 
GROUP BY 1
HAVING FEE BETWEEN 500000 AND 2000000
ORDER BY 3 DESC, 2, 1 DESC

 

-- Oracle

SELECT c.car_id, c.car_type, (c.daily_fee*30)*(1-p.discount_rate/100) as FEE
FROM CAR_RENTAL_COMPANY_CAR c JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h
     ON c.car_id = h.car_id 
     JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN p
     ON c.car_type = p.car_type
WHERE c.car_type IN ('세단', 'SUV')
      AND p.duration_type = '30일 이상'
      AND c.car_id NOT IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                           WHERE TO_CHAR(start_date,'YYYY-MM-DD')<='2022-11-30' 
                                 AND TO_CHAR(end_date,'YYYY-MM-DD')>='2022-11-01')
GROUP BY c.car_id, c.car_type, (c.daily_fee*30)*(1-p.discount_rate/100)
HAVING (c.daily_fee*30)*(1-p.discount_rate/100) BETWEEN 500000 AND 2000000
ORDER BY (c.daily_fee*30)*(1-p.discount_rate/100) DESC, c.car_type, c.car_id

 

프로그래머스