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

정답보기

select *
from (
    select cc.car_id, cc.car_type, truncate(cc.daily_fee * ((100 - discount_rate) / 100) * 30, 0) as fee
    from CAR_RENTAL_COMPANY_CAR cc
    join (
        select distinct car_id
        from CAR_RENTAL_COMPANY_RENTAL_HISTORY
        where car_id not in (
            select car_id
            from CAR_RENTAL_COMPANY_RENTAL_HISTORY
            where end_date >= '2022-11-01' and start_date <= '2022-11-30'
        )
    ) crh
    on cc.car_id = crh.car_id
    join (
        select *
        from CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        where duration_type = "30일 이상"
    ) cdp
    on cc.car_type = cdp.car_type
    where cc.car_type = "세단" or cc.car_type = "SUV"
) tbl
where tbl.fee >= 500000 and tbl.fee < 2000000
order by tbl.fee desc, tbl.car_type asc, tbl.car_id desc;

---

이번 문제는 잘 안풀려서 두 번에 걸쳐서 풀었다.

처음 안풀렸던 풀이는 이렇다.

1. `2022년 11월 1일부터 2022년 11월 30일까지` 대여 가능한 자동차라고 했으니까, 시작일이 `12월 1일` 이거나, 끝나는 날짜가 `10월 31일` 인것에 해당하는 자동차 목록을 구하자. 이것은 11월 중에는 대여 내역이 없는 자동차 목록이다.

2. 그 대여 가능한 목록을 CAR_RENTAL_COMPANY_CAR 테이블과 car_id로 조인한다. 요금과 타입이 있어야 요금으로 계산을 하고 타입으로 필터링 해야 되니까.

3. 조인된 테이블을 일단 두고, CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 duration_type이 where문을 통해 30일 이상일 때의 discount_rate를 추출한다.

4. 그 추출한 것을 2번에서 조인한 테이블과 car_type으로 조인한다. 왜냐하면 앞선 2번 단계에서의 조인 처리된 테이블에다가 `car_type에 따른 30일 대여시 discount_rate`를 적용한 실제 요금을 계산하기 위해서다.

5. 계산 완료된 테이블에서 세단과 suv만 찾는다.

6. 양식에 맞게 컬럼을 정리해주고, fee 컬럼에 50만 이상 ~ 200만 미만 조건을 적용해주고 정렬하면 되겠군.

---

그리고 두 번째 푼 내용은 이렇다.

앞선 2번부터 5번까지는 똑같을것 같은데, 확인해보니 날짜 범위 설정에 허점이 있어서 들어가면 안되는 car_id가 들어간다.

 

예를 들어, 시작일 `12월 1일` 이면서, 끝나는 날짜가 `10월 31일` 이라는 쿼리를 짜게 되면, 결과만 놓고 보면 잘 걸러지는 것 같은데, 문제는 대여 내역 테이블의 특성을 제대로 파악하지 못했다는 것이다.

 

대여 내역 테이블에는 같은 아이디로 여러 대여 내역이 들어간다. 즉, 하나의 아이디로 어떤 경우는 9월부터 12월까지 대여를 했다고 치고, 어떤 경우는 위 조건에 맞게 대여를 했다고 한다면, 조건에 해당하는 행이 일단은 발견되었으니 대여 가능한 car_id로 포함되게 된다. 즉,  내가 간과한 점은 대여 기간이 한 달을 훨씬 초과할 수도 있고, 그런 내역이 같은 아이디로 있을 수 있다는 것이었다.

 

한 달을 훨씬 초과하는 내역(이면서 대여 불가능한 내역)을 어떻게 걸러낼 수 있을까?

 

그걸 찾으려면, 반납일 기준으로 생각했을 때 11월중에는 당연히 안되고 12월 1일부터 반납 내역이 있는지 확인해야한다. 그런데 이렇게 따로 분리해서 생각하게 되면 너무 복잡해진다. 12월 1일부터 반납 내역이 있는 것들만 따로 확인하려면 거기에는 시작일도 12월 1일부터로 설정해야 하는데, 이런식으로 하면 조건문이 너무 많아진다.

 

일단 11월 1일부터 반납 내역이 있는지 확인을 하고, 추가 조건을 달아주는 방향으로 생각했다. 왜냐하면 일단 끝나는 날만 놓고 볼 때, 11월부터 반납 내역이 있다면 조건(반납일이 11월이면 무조건 불가능, 12월이면 시작일에 따라 가능 or 불가능)에 따라 달라지기 때문이다. 즉, end_date >= "2022-11-01" 를 처음으로 설정한다.

 

end_date를 고정했다면 start_date를 어떻게 설정해야 할까?

 

일단 end_date가 11월이기 때문에 대여 시작일 start_date가 11월이거나 그 이전부터라면 무조건 대여 불가능하다. start_date가 12월 이상인 경우는 고려할 필요가 없다. 반납일이 시작일보다 앞설 수 없으니 end_date는 당연히 12월과 같거나 클 것이고 이것은 대여 가능한 경우이다. 그래서 start_date <= "2022-11-30" 라는 조건이 나온다.

 

이 두 조건을 and로 묶으면 되지 않을까 시도했고 올바른 car_id들만이 포함되었다.

---

중간에 `2022년 11월 1일` 부터 `2022년 11월 30일` 까지라는 조건을 맞추기가 너무 어려웠다.

 

일단 풀이에 필요한 조건을 반납일 기준으로...감으로 때려맞춘 느낌이 좀 있는데, 이 조건을 시작일 기준으로 리뷰해보면 이렇다.

`시작 날짜가 11월 30일보다 작거나 같으면` 이라는 조건 하나만 놓고 보면 너무 당연하게도 알맞게 걸러낼 수 없다.

시작일이 10월이고 반납을 10월에 할 수도 있고...다른 경우도 많으니까.

근데 어떤 하나의 대여에 대해, 시작일이 12월이 된다면 그건 무조건 대여 가능한 케이스 인거니까, `11월 30일보다 작거나 같으면` 이라는 조건이, 대여 불가능한 애들을 찾기 위해 최소한으로 필요한 조건인 것이다.

그럼 이 경우 언제 끝나야 확정적으로 대여가 불가능한걸까? 그것은...시작을 언제 했든 11월 중에 대여가 끝나게 되면 그건 대여 불가능한 자동차일 것이다.

 

뭔가 써놓고 주석을 붙여가면서 풀면 이해가 되는데, 직관적으로 바로 떠오르지 않고 한참 걸려서 힘들었다.