자동차 대여 기록 별 대여 금액 구하기 / 프로그래머스, SQL, MySQL

문제

source: 프로그래머

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요.

결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

 

 

 

 

 

제출답안(MySQL)

WITH 
    ls_truck AS(SELECT *
                FROM car_Rental_company_car
                WHERE car_type = '트럭'),
                
    rent_info AS (SELECT 
                    *,
                    DATEDIFF(end_date, start_date)+1 AS rent_days,
                    CASE 
                        WHEN DATEDIFF(end_date, start_date) + 1 >= 90 THEN '90일 이상'
                        WHEN DATEDIFF(end_date, start_date) + 1 >= 30 THEN '30일 이상'
                        WHEN DATEDIFF(end_date, start_date) + 1 >= 7 THEN '7일 이상'
                        ELSE NULL END AS duration_type
                  FROM car_rental_company_rental_history),
    
    dc_truck AS (SELECT *
                 FROM car_rental_company_discount_plan
                 WHERE car_type = '트럭')


SELECT 
    history_id,
    ROUND(IFNULL(daily_fee, 0) * rent_days * ( 1 - IFNULL(discount_rate, 0) * 0.01 )) AS fee

FROM 
    ls_truck AS ls_truck
    LEFT JOIN rent_info AS rent_info
        ON ls_truck.car_id = rent_info.car_id
    
    LEFT JOIN dc_truck AS dc_truck
        ON rent_info.duration_type = dc_truck.duration_type
    
ORDER BY
    fee DESC, history_id DESC

 

 

 

풀이(MySQL)

3개의 CTE 생성

CTE1. 트럭의 리스트

WITH 
    ls_truck AS(SELECT *
                FROM car_Rental_company_car
                WHERE car_type = '트럭'),

 

CTE2. 렌트 기간과 duration_type이 추가된 렌트내역

    rent_info AS (SELECT 
                    *,
                    DATEDIFF(end_date, start_date)+1 AS rent_days,
                    CASE 
                        WHEN DATEDIFF(end_date, start_date) + 1 >= 90 THEN '90일 이상'
                        WHEN DATEDIFF(end_date, start_date) + 1 >= 30 THEN '30일 이상'
                        WHEN DATEDIFF(end_date, start_date) + 1 >= 7 THEN '7일 이상'
                        ELSE NULL END AS duration_type
                  FROM car_rental_company_rental_history),

 

CTE3. 트럭의 디스카운트 플랜 

    dc_truck AS (SELECT *
                 FROM car_rental_company_discount_plan
                 WHERE car_type = '트럭')

 

 

 

차 리스트를 기준으로 LEFT JOIN 진행

SELECT 
    history_id,
    ROUND(IFNULL(daily_fee, 0) * rent_days * ( 1 - IFNULL(discount_rate, 0) * 0.01 )) AS fee

FROM 
    ls_truck AS ls_truck
    LEFT JOIN rent_info AS rent_info
        ON ls_truck.car_id = rent_info.car_id
    
    LEFT JOIN dc_truck AS dc_truck
        ON rent_info.duration_type = dc_truck.duration_type
    
ORDER BY
    fee DESC, history_id DESC

>> 트럭 리스트 중 렌트가 된 내역이 없다면 daily_fee가 Null이 생성된다. 따라서 IFNULL(daily_fee, 0)을 통해 null발생하는 경우를 0으로 예외처리

 

>> 렌트기간이 30일 미만일 경우 duration_type에 Null이 생성되낟. 따라서 IFNULL(discount_rate, 0)을 통해 null이 발생하는 경우를 0으로 예외처리

 

 

 

문제 바로가기(MySQL)