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

<어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR 테이블>

<자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블>

<자동차 종류 별 대여 기간 종류 별 할인 정책 정보를 담은 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블 >

---

<문제>

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 a.car_id,
       a.car_type,
       round(a.daily_fee*30*(100-c.discount_rate/100)) as fee
from car_rental_company_car a
     inner join car_rental_company_rental_history b on a.car_id=b.car_id
     inner join car_rental_company_discount_plan c on a.car_type=c.car_type
where a.car_id in (
      select car_id
      from car_rental_company_rental_history
      where end_date >= '2022-11-01' and start_date <= '2022-11-30')
      and c.duration_type like '30%'
group by a.car_id
having a.car_type in ('세단', 'SUV')
       and (fee >= 500000 and fee < 2000000)
order by fee desc, car_type, car_id desc

실행 결과 안나옴 ... 무슨 문제일까

 

<문제 풀이>

select a.car_id as car_id,
       a.car_type as car_type,
       round(a.daily_fee*30*(100-c.discount_rate)/100) as fee
from car_rental_company_car a 
     inner join car_rental_company_rental_history b on a.car_id=b.car_id
     inner join car_rental_company_discount_plan c on a.car_type=c.car_type
where a.car_id not in (
      select car_id
      from car_rental_company_rental_history
      where end_date >= '2022-11-01' and start_date <= '2022-12-01')
            and c.duration_type like '30%'
group by a.car_id
having a.car_type in ('세단', 'SUV')
       and (fee >= 500000 and fee < 2000000)
order by fee desc, car_type, car_id desc

 

---

<설명>

1. 3개의 테이블을 사용하여 car_id 기준, car_type 기준으로 join 결합

▶ from car_rental_company_car a 
     inner join car_rental_company_rental_history b on a.car_id=b.car_id
     inner join car_rental_company_discount_plan c on a.car_type=c.car_type

 

2. 대여 가능한 2022-11-01~2022-12-01에 대여가 가능한 자동차 목록을 가져와야 함 --> not in을 사용하여 해당 기간에 렌탈 기록이 없는 car_id를 가져오기

▶ where a.car_id not in ( select car_id
                                           from car_rental_company_rental_history
                                           where end_date >= '2022-11-01' and start_date <= '2022-12-01')

 

3. 문제에 나와있듯이 대여 기간이 30일 이상인 것을 검색

▶ and c.duration_type like '30%' (아까의 where절과 이어서)

 

4. 문제에서 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력

- (100 - 할인율)/100 = 내야할 비율 --> (100-c.discount_rate)/100)

- 대여료 * 30일 * 내야할 비율 = 요금 --> (a.daily_fee*30*(100-c.discount_rate)/100)

▶ select a.car_id as car_id,
       a.car_type as car_type,
       round(a.daily_fee*30*(100-c.discount_rate)/100) as fee --> 지불비용

 

5.. 자동차 아이디 기준으로 그룹화하고 자동차 종류가 세단과 SUV인 것만 가져오기

▶ group by a.car_id
    having a.car_type in ('세단', 'SUV')

 

6. 30일간의 대여 금액이 50만원 ~ 200만원 미만인 자동차로 가져오기

▶ and (fee >= 500000 and fee < 2000000)

 

7. 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬

▶ order by fee desc, car_type, car_id desc

 

---

이미지 출처: https://velog.io/@gayeong39/프로그래머스-특정-기간동안-대여-가능한-자동차들의-대여비용-구하기

 

이 문제는 다시 한 번 복습해봐야 할 것 같다
점점 생각할 게 많아지는 SQL .....