★[String,DATE / level4] 자동차 대여 기록 별 대여 금액 구하기

오답쿼리

# SELECT history_id AS HISTORY_ID 
#     , AS FEE
# FROM CAR_RENTAL_COMPANY_CAR
# LIMIT FEE DESC, HISTORY_ID DESC

/*  트럭 CAR_iD
SELECT car_id
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = '트럭' -- 1,6,8,12,20,30
*/

SELECT history.history_id AS HISTORY_ID
    , (DATEDIFF(history.end_date, history.start_date)+1) 
    * car.daily_fee 
    * (1- 
       ( CASEWHEN  DATEDIFF(history.end_date, history.start_date)+1 >=7 THEN 0.05
         CASEWHEN DATEDIFF(history.end_date, history.start_date)+1 >=30 THEN 0.07
         CASEWHEN DATEDIFF(history.end_date, history.start_date)+1 >=90 THEN 0.1 END) 
      )        
    AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS car
    INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS history
        ON car.car_id = history.car_id
WHERE car.car_id IN 
                    (SELECT car_id
                    FROM CAR_RENTAL_COMPANY_CAR
                    WHERE CAR_TYPE = '트럭')

정답쿼리

WITH table1 AS(
    SELECT history.history_id AS HISTORY_ID
        ,DATEDIFF(history.end_date, history.start_date)+1 AS diff
        ,car.daily_fee AS daily_fee
        ,CASE WHEN  DATEDIFF(history.end_date, history.start_date)+1 <7 THEN 1
        WHEN DATEDIFF(history.end_date, history.start_date) <30 THEN 0.95
        WHEN DATEDIFF(history.end_date, history.start_date) <90 THEN 0.92
        ELSE 0.85 END AS counta
    FROM CAR_RENTAL_COMPANY_CAR AS car
        INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS history
            ON car.car_id = history.car_id
    WHERE car.CAR_TYPE = '트럭'

)

SELECT HISTORY_ID
    , ROUND(diff*daily_fee*counta,0) AS FEE
FROM  table1
ORDER BY FEE DESC, HISTORY_ID DESC

 

 

오답노트

1. CASE WHEN 띄어쓰기 해야 한다. 그 뒤에 조건을 적고 싶으면 WHEN만 덧붙여주면 됨.

2. 그냥 할인율을 숫자로 적었는데, 칼럼명으로 적고 싶은데 정말 어렵다. => 다시 풀어봐야할듯

 

 

 

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