프로그래머스 | 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (MySQL)

 

오늘도 오랑이는 문제를 풉니다.

 

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

문제

CAR_RENTAL_COMPANY_CAR

CAR_RENTAL_COMPANY_RENTAL_HISTORY

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 

 

1. 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 WHERE CAR_TYPE IN ('세단', 'SUV')
2. 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 START_DATE, END_DATE 조건
3. 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 FEE BETWEEN 50만 AND 200만
4. 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력 SELECT CAR_ID, CAR_TYPE, FEE
5. 결과는 대여 금액 내림차순, 자동차 종류 오름차순, 자동차 ID 내림차순 정렬 ORDER BY 3 DESC, 2, 1 DESC

 

풀이 과정

우선 테이블별로 각 조건에 대한 서브쿼리문을 작성해봅니다.

 

1. 자동차 종류에 대한 조건

SELECT CAR_ID, CAR_TYPE, DAILY_FEE 
FROM CAR_RENTAL_COMPANY_CAR 
WHERE CAR_TYPE IN ('세단', 'SUV')

 

2. 대여 가능 여부에 대한 조건

SELECT CAR_ID, START_DATE, END_DATE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE START_DATE > '2022-11-30' OR END_DATE < '2022-11-01'

 

3. 할인율을 적용한 대여금액에 대한 조건

SELECT CAR_TYPE, (100-DISCOUNT_RATE)*0.01 AS RATE 
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
WHERE CAR_TYPE IN ('세단', 'SUV') AND DURATION_TYPE = '30일 이상'

 

세 조건을 모두 이어서 전체 쿼리를 짜보면 아래와 같은데 정답은 틀렸다고 나옵니다.

그래서 다시 단계별로 생각해보니 2 대여 가능 날짜에 대한 조건이 잘못 되었다는 것을 알았습니다. 

 

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

 

위에 설정한 조건대로 코드를 실행해보면 아래의 결과가 나옵니다. 하지만 전체 데이터와 비교해 봤을 때, CAR_ID 28의 경우 실제 11월에 대여가 불가능한데도 조건문 쿼리에 포함돼서 출력된 것이 보입니다. 각 대여 경우에 대해 개별 값만 제외되기 때문에 CAR_ID 에 묶여서 하나라도 대여 불가능한 경우 전체 CAR_ID 가 빠지게 조건을 재설정해야 합니다. 

조건문 쿼리

전체 데이터

 

대여 가능 여부에 대한 조건을 서브쿼리로 다시 작성하면 아래와 같습니다. 과정을 살펴볼까요?

SELECT CAR_ID 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY 1 
HAVING SUM(IF(START_DATE > '2022-11-30' OR END_DATE < '2022-11-01',0,1))  = 0

 

IF(START_DATE > '2022-11-30' OR END_DATE < '2022-11-01',0,1) AS A 로 작성해서 새로운 칼럼을 만들어줍니다.

11월에 대여 가능한 경우 0, 불가한 경우 1로 값이 지정됩니다. 

해당 값을 CAR_ID 기준으로 모두 더했을 때 11월에 대여가 가능하려면 0이 나와야 합니다. 

편의상 조건문을 SELECT 에 넣었지만, 최종 코드에서 WHERE CAR_ID IN (서브쿼리) 로 써야 하기 때문에 HAVING으로 넣어줍니다. 

 

 

최종 쿼리

SELECT CAR_ID, C.CAR_TYPE, ROUND((30*DAILY_FEE*RATE)) AS FEE
FROM (SELECT CAR_ID, CAR_TYPE, DAILY_FEE FROM CAR_RENTAL_COMPANY_CAR 
    	WHERE CAR_TYPE IN ('세단', 'SUV')) C
    JOIN (SELECT CAR_TYPE, (100-DISCOUNT_RATE)*0.01 AS RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
      	  WHERE CAR_TYPE IN ('세단', 'SUV') AND DURATION_TYPE = '30일 이상') D ON C.CAR_TYPE = D.CAR_TYPE
WHERE CAR_ID IN 
      (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
      GROUP BY 1 HAVING SUM(IF(START_DATE > '2022-11-30' OR END_DATE < '2022-11-01',0,1))  = 0)
      AND (30*DAILY_FEE*RATE) >= 500000 AND (30*DAILY_FEE*RATE) < 2000000
ORDER BY 3 DESC, 2, 1 DESC ;

 

최종 쿼리를 좀 더 깔끔하고 효율적으로 다듬어볼 수 있습니다. 

SELECT C.CAR_ID, C.CAR_TYPE, ROUND((30*DAILY_FEE*RATE)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C  -- 중복 조건 제거 
    JOIN (SELECT CAR_TYPE, (100-DISCOUNT_RATE)*0.01 AS RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
         WHERE CAR_TYPE IN ('세단', 'SUV') AND DURATION_TYPE = '30일 이상') D ON C.CAR_TYPE = D.CAR_TYPE
    JOIN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
         GROUP BY 1 HAVING SUM(IF(START_DATE > '2022-11-30' OR END_DATE < '2022-11-01',0,1))  = 0) H
         ON C.CAR_ID = H.CAR_ID  -- where subquery 를 join 으로 변경
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY 3 DESC, 2, 1 DESC ;

 

 

오랑우탄이 영어를 하고 오랑이가 쿼리마스터가 되는 그날까지~

 

 

프로그래머스