[SQL] 자동차 대여 기록 별 대여 금액 구하기 - 프로그래머스

문제

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

핵심

1. 자동차 타입이 '트럭'인 데이터

2. 대여 기록 별로 대여 금액(컬럼명: FEE) 기록 ID와 대여 금액 리스트를 출력

3. 대여 금액을 기준으로 내림차순 정렬, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬

구현

1. WHERE 절을 이용하여 '트럭' 분리

WHERE car.car_type = '트럭'

2. 대여 기간 별 금액 산정하고, 해당 금액에 할인율을 적용해줘야 합니다.

우선, 하루당 금액이 얼마인지, 그리고 얼마나 많은 기간동안 대여했는지를 산출해줍니다.

이 때, 서브 쿼리를 사용하게 되면 너무 복잡해지므로, WITH 절을 활용하여 상단에 CTE(Common Table Expression)를 생성해줍니다.

WITH cte_table AS (
    SELECT
    H.history_id,
    C.car_type,
    C.daily_fee,
    DATEDIFF(H.end_date,H.start_date) + 1 AS duration
    FROM car_rental_company_rental_history H
    INNER JOIN car_rental_company_car C ON H.car_id = C.car_id
    WHERE C.car_type = '트럭'
)

하지만, 위와 같이 생성된 cte 테이블로 할인율 테이블과 JOIN 하기 위해서는 car_type 만으로는 매칭되는 데이터를 정상적으로 조회할 수 없습니다. 따라서, duration_type 이라는 컬럼도 함께 생성하여 처리해줘야 Join이 가능합니다.

WITH cte_table AS (
SELECT
    H.history_id,
    C.car_type,
    C.daily_fee,
    DATEDIFF(H.end_date,H.start_date) + 1 AS duration,
    CASE 
    WHEN DATEDIFF(H.end_date, H.start_date) + 1 >= 90 THEN "90일 이상"
    WHEN DATEDIFF(H.end_date, H.start_date) + 1 >= 30 THEN "30일 이상"
    WHEN DATEDIFF(H.end_date, H.start_date) + 1 >= 7 THEN "7일 이상"
    ELSE 'NONE' END duration_type
    FROM car_rental_company_rental_history H
    INNER JOIN car_rental_company_car C ON H.car_id = C.car_id
    WHERE C.car_type = '트럭'
)

위처럼 duration_type 이라는 컬럼을 추가해줬습니다.

그 다음으로, CTE를 활용하여 할인율 테이블과 조인해줍니다.

SELECT cte_table.history_id, 
    ROUND(cte_table.daily_fee * cte_table.duration * 
          (100 - IFNULL(P.discount_rate,0)) / 100) fee
FROM cte_table
LEFT JOIN car_rental_company_discount_plan P 
    ON P.duration_type = cte_table.duration_type 
    AND P.car_type = cte_table.car_type

위 코드를 보면, LEFT JOIN 을 수행한 것을 알 수 있습니다. 이를 만약, INNER JOIN으로 수행한다면, 할인율이 적용되지 않은 7일 미만의 데이터들은 트럭 대여 데이터임에도 불구하고, 조인 테이블에서 제외됩니다.

따라서, cte를 기준으로 '트럭'이고 대여 기록이 있는 경우, 할인율이 없더라도 정산되어 조인 테이블에 출력되도록 해줘야 합니다.

결국, 위 2번 조건대로 할인율이 적용된 정산 대여 금액이 나옵니다.

마지막으로, 위 3번 조건대로 정렬을 해줍니다.

ORDER BY 2 DESC, 1 DESC;

위 정수가 의미하는 것은 컬럼의 순서를 의미합니다. 위 조인된 테이블에서 history_id 가 첫번째, fee 컬럼이 두번째에 위치하므로 정수는 각 컬럼의 순서를 의미한다고 보시면 됩니다.

제출

WITH cte_table AS (
    SELECT
    H.history_id,
    C.car_type,
    C.daily_fee,
    DATEDIFF(H.end_date,H.start_date) + 1 AS duration,
    CASE 
    WHEN DATEDIFF(H.end_date, H.start_date) + 1 >= 90 THEN "90일 이상"
    WHEN DATEDIFF(H.end_date, H.start_date) + 1 >= 30 THEN "30일 이상"
    WHEN DATEDIFF(H.end_date, H.start_date) + 1 >= 7 THEN "7일 이상"
    ELSE 'NONE' END duration_type
    FROM car_rental_company_rental_history H
    INNER JOIN car_rental_company_car C ON H.car_id = C.car_id
    WHERE C.car_type = '트럭'
)

SELECT cte_table.history_id, 
    ROUND(cte_table.daily_fee * cte_table.duration * 
          (100 - IFNULL(P.discount_rate,0)) / 100) fee
FROM cte_table
LEFT JOIN car_rental_company_discount_plan P 
    ON P.duration_type = cte_table.duration_type 
    AND P.car_type = cte_table.car_type
ORDER BY 2 DESC, 1 DESC;