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

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

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

https://school.programmers.co.kr/learn/courses/30/lessons/151141

프로그래머스

단계별로 풀자

 

1. CAR_RENTAL_COMPANY_CAR 테이블 ==> CAR_ID CAR_TYPE =  자동차 종류 트럭, DAILY_FEE 뽑기

SELECT CAR_ID, CAR_TYPE , DAILY_FEE FROM CAR_RENTAL_COMPANY_CAR 
WHERE CAR_TYPE = '트럭'

2. CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블 ==> HISTORY_ID, CAR_ID , END_DATE- START_DATE + 1 뽑기

 SELECT HISTORY_ID, CAR_ID , 
     CASE WHEN (END_DATE- START_DATE + 1) >= 90 THEN '90일 이상'
          WHEN (END_DATE- START_DATE + 1) >= 30 THEN '30일 이상'
          WHEN (END_DATE- START_DATE + 1) >= 7 THEN '7일 이상'
          ELSE '7일 이하'
          END AS DURATION 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY

3. CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블 트럭, DISCOUNT_RATE 뽑기

SELECT CAR_TYPE , DURATION_TYPE , DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE CAR_TYPE = '트럭'

4. JOIN 하기_1

SELECT B.HISTORY_ID , B.CAR_ID , B.DURATION, B.DURATION_1 ,A.CAR_TYPE , A.DAILY_FEE FROM
(SELECT CAR_ID, CAR_TYPE , DAILY_FEE FROM CAR_RENTAL_COMPANY_CAR 
WHERE CAR_TYPE = '트럭') A LEFT JOIN

(SELECT HISTORY_ID, CAR_ID , 
    CASE WHEN (END_DATE- START_DATE + 1) >= 90 THEN '90일 이상'
         WHEN (END_DATE- START_DATE + 1) >= 30 THEN '30일 이상'
         WHEN (END_DATE- START_DATE + 1) >= 7 THEN '7일 이상'
         ELSE '7일 이하'
         END AS DURATION,
    (END_DATE- START_DATE + 1) AS DURATION_1
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) B 

ON A.CAR_ID = B.CAR_ID

5. JOIN 하기_2

SELECT A.HISTORY_ID , A.CAR_ID , A.DURATION, A.DURATION_1 , A.CAR_TYPE , A.DAILY_FEE , 

CASE WHEN B.DISCOUNT_RATE IS NULL THEN 0
    ELSE B.DISCOUNT_RATE END AS DISCOUNT_RATE

FROM
(SELECT B.HISTORY_ID , B.CAR_ID , B.DURATION, B.DURATION_1 ,A.CAR_TYPE , A.DAILY_FEE FROM
(SELECT CAR_ID, CAR_TYPE , DAILY_FEE FROM CAR_RENTAL_COMPANY_CAR 
WHERE CAR_TYPE = '트럭') A LEFT JOIN

(SELECT HISTORY_ID, CAR_ID , 
    CASE WHEN (END_DATE- START_DATE + 1) >= 90 THEN '90일 이상'
         WHEN (END_DATE- START_DATE + 1) >= 30 THEN '30일 이상'
         WHEN (END_DATE- START_DATE + 1) >= 7 THEN '7일 이상'
         ELSE '7일 이하'
         END AS DURATION,
    (END_DATE- START_DATE + 1) AS DURATION_1
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) B 

ON A.CAR_ID = B.CAR_ID) A LEFT JOIN 

(SELECT CAR_TYPE , DURATION_TYPE , DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE CAR_TYPE = '트럭') B 

ON A.CAR_TYPE = B.CAR_TYPE AND A.DURATION = B.DURATION_TYPE

6. 최종

SELECT HISTORY_ID , (DAILY_FEE*(1-(DISCOUNT_RATE*0.01))*DURATION_1) AS FEE FROM

(SELECT A.HISTORY_ID , A.CAR_ID , A.DURATION, A.DURATION_1 , A.CAR_TYPE , A.DAILY_FEE , 

CASE WHEN B.DISCOUNT_RATE IS NULL THEN 0
    ELSE B.DISCOUNT_RATE END AS DISCOUNT_RATE

FROM
(SELECT B.HISTORY_ID , B.CAR_ID , B.DURATION, B.DURATION_1 ,A.CAR_TYPE , A.DAILY_FEE FROM
(SELECT CAR_ID, CAR_TYPE , DAILY_FEE FROM CAR_RENTAL_COMPANY_CAR 
WHERE CAR_TYPE = '트럭') A LEFT JOIN

(SELECT HISTORY_ID, CAR_ID , 
    CASE WHEN (END_DATE- START_DATE + 1) >= 90 THEN '90일 이상'
         WHEN (END_DATE- START_DATE + 1) >= 30 THEN '30일 이상'
         WHEN (END_DATE- START_DATE + 1) >= 7 THEN '7일 이상'
         ELSE '7일 이하'
         END AS DURATION,
    (END_DATE- START_DATE + 1) AS DURATION_1
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) B 

ON A.CAR_ID = B.CAR_ID) A LEFT JOIN 

(SELECT CAR_TYPE , DURATION_TYPE , DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE CAR_TYPE = '트럭') B 

ON A.CAR_TYPE = B.CAR_TYPE AND A.DURATION = B.DURATION_TYPE)

ORDER BY FEE DESC , HISTORY_ID DESC