02/13 SQL 문제풀이(🌟🌟🌟🌟🌟)

문제

• 프로그래머스 SQL 문제
• 자동차 대여 기록 별 대여 금액 구하기 / 레벨 4
• 문제 내용 : 하단 프로그래머스 문제 [링크]
  
  
  
  

내가 작성한 Query

WITH TEMP_01 AS(
    SELECT A.HISTORY_ID, DATEDIFF(A.END_DATE, A.START_DATE) + 1 AS DAYS
    , CASE WHEN DATEDIFF(A.END_DATE, A.START_DATE) + 1 >= 90 THEN '90일 이상'
           WHEN DATEDIFF(A.END_DATE, A.START_DATE) + 1 >= 30 THEN '30일 이상'
           WHEN DATEDIFF(A.END_DATE, A.START_DATE) + 1 >= 7 THEN '7일 이상'
           END AS DURATION
    , B.CAR_TYPE, B.DAILY_FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY A
    JOIN CAR_RENTAL_COMPANY_CAR B ON A.CAR_ID = B.CAR_ID
WHERE B.CAR_TYPE = '트럭'
)
SELECT A.HISTORY_ID
    , ROUND((DAILY_FEE * (1-COALESCE(B.DISCOUNT_RATE, 0)/100))*A.DAYS, 0) AS FEE
FROM TEMP_01 A
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B ON A.CAR_TYPE = B.CAR_TYPE AND A.DURATION = B.DURATION_TYPE
ORDER BY FEE DESC, HISTORY_ID DESC

• 차량 대여 정보 테이블과(CAR_RENTAL_COMPANY_RENTAL_HISTORY), 차량정보 테이블 (CAR_RENTAL_COMPANY_CAR)을 'CAR_ID' 기준으로 JOIN 해준다.

• WHERE 절에 자동차 종류(CAR_TYPE)가 '트럭'인 데이터만 필터링한다.

• DATEDIFF(A.END_DATE, A.START_DATE) + 1을 이용해 대여 일수를 구해주고 이를 기준으로 '90일 이상', '30일 이상', '7일 이상'의 DURATION 정보를 CASE WHEN THEN을 이용해 구한다.(대여일, 반납일 모두 대여기간에 포함되어야 하므로, 양 경계값 포함하기 위해 +1 해줌)

• 위에서 구한 테이블을 TEMP_01로 만든다.

• TEMP_01 테이블과 할인 정보가 포함된 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블을 CAR_TYPE과 위에서 CASE문으로 구한 DURATION을 기준으로 LEFT JOIN 해 준다.

• 🌟🌟🌟(왜 LEFT JOIN인가? : 7일 이하의 대여 기록도 구해야 하니까 LEFT JOIN이다. CAR_RENTAL_COMPANY_DISCOUNT_PLAN에는 7일 이하의 할인정보는 없기 때문에 그냥 JOIN을 할 경우 7일 이하의 대여기록이 포함되지 않는다.)

• 각 DURATION에 맞는 할인율(DISCOUNT_RATE)를 반영한 가격을 구해준다.

• DAILY_FEE*(1-COALESCE(DISCOUNT_RATE, 0)/100)*DAYS를 통해 각 할인율을 반영한 일별 요금을 구하고, 대여 일수를 곱해준다. COALESCE를 사용하였기 때문에 할인율이 없는 데이터(즉, 7일 미만의 대여기록)는 할인이 없는 금액을 구하게 된다.

• 위에서 구한 총 요금 컬럼을 FEE 컬럼으로 별칭 준다.

• ORDER BY를 이용해 FEE, HISTORY_ID 기준으로 내림차순 정렬한다.

• 🌟🌟🌟 1) LEFT JOIN과 JOIN을 비교,
  2) CASE WHEN THEN에서 데이터를 처리하는 순서,
  3) DAFE_DIFF를 통해 정확한 대여 일수를 구하는 법
  4) COALESCE 함수를 통해 NULL 값에 대한 처리
  5) TEMP_01 등 임시 테이블을 통해 문제를 해결하는 방법
  모든 것을 배울 수 있던 문제였다. 중요중요!