[SQL] Programmers 문제 풀기(자동차 대여 기록 별 대여 금액 구하기)

해당 문제는 Programmers에서 사용한 문제이며 모든 테이블의 자료와 출처는 Programmers임을 밝힙니다.

프로그래머스

해당 글에서는 WITH, CASE, GROUP BY, JOIN을 이용하여 푸는 문제로 초보자가 하기에는 보통~어려움 정도의 난이도를 가지고 있는 것 같습니다.

해당 문제 원본을 확인 하시려면 해당 사이트를 들어가시면 될 것 같습니다.

 

시작하겠습니다.

---

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

해당 문제는 지금까지 사용했던 모든 것들을 사용하면 됩니다. 

이 문제에서 알아둬야 할 것은 CAST()입니다. 해당 CAST를 사용하여 문자열에 있는 숫자를 뽑아 내야 하기 때문입니다.

CAST('3월' AS UNSIGNED) 이렇게 설정해주시면 3을 출력하게 되는 것입니다.

그리고 나서 문제 조건에 맞게 따라가면서 JOIN해주면 됩니다.

 

1. 차량 타입이 트럭이면서 대여한 날짜 구하기

WITH CTE AS (
    SELECT 
        b.HISTORY_ID, 
        DATEDIFF(b.END_DATE, b.START_DATE) + 1 AS RENTAL_DAYS,
        a.CAR_TYPE,
        a.DAILY_FEE
    FROM 
        CAR_RENTAL_COMPANY_CAR a
    JOIN 
        CAR_RENTAL_COMPANY_RENTAL_HISTORY b ON a.CAR_ID = b.CAR_ID
    WHERE 
        a.CAR_TYPE = '트럭'
)

코드 결과

첫째날도 대여 날짜로 포함 시키므로 +1을 해주었습니다. 그리고 CTE라는 가상 테이블을 만들어 주었습니다.

---

2. CAST 사용하여 일 수에 맞는 대여 날짜 뽑아내기

CTE2 AS (
    SELECT CAST(DURATION_TYPE AS UNSIGNED) AS DURATION
         , DISCOUNT_RATE
         , CAR_TYPE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
    WHERE 
        CAR_TYPE = '트럭'
)

코드 결과

이미지와 같이 CAST를 사용하여 '7일 이상'인 문자열은 숫자 7로만 출력된 것을 볼 수 있습니다.

이제 해당 테이블을 CTE2를 사용하여 날짜 비교가 가능할 것 같습니다.

---

3. 대여날짜를 확인 후 비교하여 해당 날짜에 맞는 할인률 적용하고 계산하기

CTE3 AS (SELECT 
    a.HISTORY_ID,
    CASE
        WHEN a.RENTAL_DAYS >= c.DURATION THEN CAST((a.DAILY_FEE * (1 - c.DISCOUNT_RATE / 100)) * a.RENTAL_DAYS AS UNSIGNED)
        ELSE CAST(a.DAILY_FEE * a.RENTAL_DAYS AS UNSIGNED)
    END AS FEE
FROM 
    CTE a
    JOIN
    CTE2 c ON a.car_type = c.car_type
)

코드 결과

우선 해당 코드에서 CASE 문을 활용하여 대여날짜와 2번에서 뽑아낸 숫자를 비교해줍니다.

 

예를 들어 a.RENTAL_DAYS가 40일이라면 c.DURATION에는 7, 30, 90이 있게 됩니다. 그리고 두 컬럼을 비교를 합니다.

그럼 40 >= 7 : True, 40 >= 30 True, 40 >= 90 False 이므로 7일 이상 할인율과 30일 이상 할인율을 적용하면 되는 것입니다.

 

이렇게 계산을 해주면 같은 HISTORY_ID와 같은 값들이 3개 존재를 하게 됩니다. 왜냐하면 True가 아니라면 ELSE문으로 인해 할인 없이 적용되어 출력되기 때문입니다. 

 

그럼 저희는 이 중 할인이 가장 많이 된 값을 찾으면 되는 것입니다!!

---

4. GROUP BY를 통해 할인이 제일 많이 된 값 찾기

WITH CTE AS (
    SELECT 
        b.HISTORY_ID, 
        DATEDIFF(b.END_DATE, b.START_DATE) + 1 AS RENTAL_DAYS,
        a.CAR_TYPE,
        a.DAILY_FEE
    FROM 
        CAR_RENTAL_COMPANY_CAR a
    JOIN 
        CAR_RENTAL_COMPANY_RENTAL_HISTORY b ON a.CAR_ID = b.CAR_ID
    WHERE 
        a.CAR_TYPE = '트럭'
),
CTE2 AS (
    SELECT CAST(DURATION_TYPE AS UNSIGNED) AS DURATION
         , DISCOUNT_RATE
         , CAR_TYPE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
    WHERE 
        CAR_TYPE = '트럭'
),
CTE3 AS (SELECT 
    a.HISTORY_ID,
    CASE
        WHEN a.RENTAL_DAYS >= c.DURATION THEN CAST((a.DAILY_FEE * (1 - c.DISCOUNT_RATE / 100)) * a.RENTAL_DAYS AS UNSIGNED)
        ELSE CAST(a.DAILY_FEE * a.RENTAL_DAYS AS UNSIGNED)
    END AS FEE
FROM 
    CTE a
    JOIN
    CTE2 c ON a.car_type = c.car_type
)
    
SELECT HISTORY_ID, MIN(FEE) AS FEE
FROM CTE3
GROUP BY HISTORY_ID
ORDER BY 
    FEE DESC, 
    HISTORY_ID DESC

위의 코드와 같이 GROUP BY를 통해 묶어준 뒤 가격이 제일 낮은 값( = 할인이 제일 많이 된 값)을 찾아주면 됩니다.

 

그리고 조건에 맞게 정렬해주면 해당 코드는 정답이 됩니다.

---

이상으로 Programmers 문제 풀이를 마치도록 하겠습니다.