문제

75번. 자동차 대여 기록 별 대여 금액 구하기

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

(링크)

문제 분석 및 구상

1. 자동차 종류가 ‘트럭’이다 (car_type = ‘트럭’으로 고정)

2. 대여 금액(as ‘FEE’)를 구해야 한다 (fee = daily_fee (1-(discount_rate/100)) duration_date)
   duration_date = datediff(end_date, start_date) +1 (자동차 대여는 대여한 당일부터 count를 해야한다)

3. SELECT는 history_id, fee

4. ORDER BY fee desc, plan_id desc

5. 풀 때는 의식하지 않았었는데, duration_type의 값을 하드코딩하지 않고 변동되더라도 풀이가 되도록 코드를 작성했다. 문제에서 요구한 바는 아니었기에 문제 해결에 초점을 둔다면 불필요한 고민이었지만, 개인적으론 이런 챌린징이 마음에 들긴 하다. 시간만 여유가 있다면 참 좋을텐데.

최종안

WITH rent AS (
    SELECT
        history_id
        , car_id
        , datediff(end_date, start_date)+1 as "duration_date"
    FROM
        CAR_RENTAL_COMPANY_RENTAL_HISTORY
),
aa AS (
    SELECT
        c.car_id
        , c.car_type
        , c.daily_fee
        , cast(replace(d.duration_type, '일 이상', "") as float) as 'n_duration_type'
        , d.discount_rate
        , rent.history_id
        , rent.duration_date
    FROM
        CAR_RENTAL_COMPANY_CAR c
        INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d
            ON (c.car_type = d.car_type)
                AND (c.car_type = '트럭')
        INNER JOIN rent
            ON (c.car_id = rent.car_id)
),
bb AS (
    SELECT
        history_id
        , daily_fee
        , CASE
            WHEN duration_date < 7 THEN 0
            ELSE discount_rate
        END as 'n_discount_rate'
        , duration_date
        , rank() over (partition by history_id order by (n_duration_type <= duration_date) desc, n_duration_type desc)as 'ranking'
    FROM aa
)
SELECT
    history_id
    , round(daily_fee * (1-(n_discount_rate/100)) * duration_date,0) as 'FEE'
FROM
    bb
WHERE
    ranking = 1
ORDER BY
    fee desc
    , history_id desc
;

풀이 과정

WITH rent

차량 대여 기간을 추출하기 위한 간단한 코드

WITH aa

car_type = '트럭'인 조건을 건 상태로,
이후 필요한 모든 컬럼들을 추출

replace 함수의 경우 결과물이 string type이기 때문에 추후 비교 연산자를 사용할 때 오류가 발생했다
이를 해결하기 위해 CAST 함수로 실수형float으로 변경

WITH bb

n_discount_rate는 대여기간이 7일 미만이면 0으로, 그 외는 그대로 나오도록 처리했다

rank() 함수의 경우 history_id로 파티션을 나눈 뒤, 2개의 order by를 달았다
특히 첫번째 (n_duration_type <= duration_date)는 SQL에서도 불리언 값을 활용할 수 있다는 것을 알게 되어서 활용해보았다

대여 기간이 duration_type의 조건을 넘었다면 True, 즉 1로
넘지 못했다면 False, 즉 0으로 나타난다 (위 이미지 우측에서 2번째 컬럼 참고)

이를 통해 일단 True인 값들을 위로 몰고, 여기서 duration_type의 숫자를 또 내림차순으로 정렬했다

메인 쿼리

앞서 bb에서 ranking = 1만 출력을 함으로써 '대여기간이 조건을 만족한 경우 중 가장 조건이 높은' 경우만 남길 수 있다.

여담

여러가지로 그동안 사용하지 않았던 코드나 접근법을 활용해본 문제였다
다만 문제에서 요구한 것 이상을 구현하기 위해 불필요한 과정이 많이 첨가되었기에, 어찌보면 과했다고 볼 수 있을 듯.
또한 결과로 나온 코드도 최적화가 덜 되었다고 느껴져서, 추후 시간이 있을 때 다시 처음부터 작성하면서 보다 효율적이고 깔끔한 코드를 작성할 수 있도로 개선해볼 필요가 있겠다.