[프로그래머스/SQL] Level 4_자동차 대여 기록 별 대여 금액 구하기(STRING, DATE)

📌문제 유형

STRING, DATE

 

📌문제

프로그래머스

 

📌나의 문제풀이

SELECT A.HISTORY_ID, 
ROUND(CASE WHEN DATE >= 7 AND DATE < 30 THEN DAILY_FEE * DATE * 0.95
        WHEN DATE >= 30 AND DATE < 90 THEN DAILY_FEE * DATE * 0.92
        WHEN DATE >= 90 THEN DAILY_FEE * DATE * 0.85
        ELSE DAILY_FEE * DATE END, 0) AS FEE
FROM 
    (SELECT DISTINCT HISTORY_ID, DATEDIFF(END_DATE,START_DATE) + 1 AS DATE, DAILY_FEE
    FROM CAR_RENTAL_COMPANY_CAR AS C LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H ON C.CAR_ID = H.CAR_ID LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P ON C.CAR_TYPE = P.CAR_TYPE
    WHERE C.CAR_TYPE = '트럭') AS A
ORDER BY FEE DESC, A.HISTORY_ID DESC

 

📌다른사람의 문제풀이

SELECT HISTORY_ID, ROUND(SUM(C.DAILY_FEE*(DATEDIFF(END_DATE, START_DATE)+1)*(1-IFNULL(DISCOUNT_RATE, 0)/100)),0) AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS R
LEFT JOIN CAR_RENTAL_COMPANY_CAR AS C ON C.CAR_ID = R.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS D ON C.CAR_TYPE = D.CAR_TYPE AND
      D.DURATION_TYPE=(CASE
                       WHEN DATEDIFF(R.END_DATE, R.START_DATE)+1 BETWEEN 7 AND 29 THEN "7일 이상"
                       WHEN DATEDIFF(R.END_DATE, R.START_DATE)+1 BETWEEN 30 AND 89 THEN "30일 이상"
                       WHEN DATEDIFF(R.END_DATE, R.START_DATE)+1 >= 90 THEN "90일 이상"
                       ELSE "" END)
      WHERE C.CAR_TYPE="트럭"
GROUP BY HISTORY_ID
ORDER BY FEE DESC, R.HISTORY_ID DESC