02/15 SQL 문제풀이(🌟🌟🌟🌟) - 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 / LV.4

문제

• 프로그래머스 SQL 문제
• 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 / 레벨 4
• 문제 내용 : 하단 프로그래머스 문제 [링크]
  
  
  
  
  

내가 작성한 Query

WITH TEMP_01 AS(
SELECT B.CAR_ID, B.CAR_TYPE, B.DAILY_FEE,
      MAX(CASE WHEN A.END_DATE <= '2022-11-01' OR A.START_DATE >= '2022-11-30' THEN '대여가능'
           ELSE '대여불가능' END) AS 대여여부
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY A
    JOIN CAR_RENTAL_COMPANY_CAR B ON A.CAR_ID = B.CAR_ID
WHERE B.CAR_TYPE IN ('세단', 'SUV')
GROUP BY CAR_ID
HAVING 대여여부 = '대여가능'
), TEMP_02 AS(
SELECT C.CAR_ID, C.CAR_TYPE
        , ROUND(C.DAILY_FEE * (1-D.DISCOUNT_RATE/100)*30, 0) AS FEE
FROM TEMP_01 C
    JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D ON C.CAR_TYPE = D.CAR_TYPE
WHERE D.DURATION_TYPE = '30일 이상'
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC)
SELECT *
FROM TEMP_02
WHERE FEE BETWEEN 500000 AND 2000000

• 먼저, 차량 정보(CAR_RENTAL_COMPANY_CAR) 테이블과 차량 대여 정보(CAR_RENTAL_COMPANY_RENTAL_HISTORY) 테이블을 JOIN해 준다.

• WHERE 절에서 CAR_TYPE이 '세단', 'SUV'인 차량 데이터를 필터링해준다.

• 차량 ID 별로 2022-11-01 ~ 2022-11-30 에 대여가 가능한 차량 데이터를 선택하기 위해서 우선 CAR_ID 기준으로 GROUP BY 해 준다.

• SELECT 절에서, 대여 여부라는 컬럼을 만들고 차량 기록중에 END_DATE가 '2022-11-01' 이전인 기록이나 START_DATE가 '2022-11-30' 이후인 데이터들은 '대여 가능', 그 외에 데이터는 '대여불가능' 이라고 출력해준다.

• MAX() 함수를 통해, 차량별로 대여가 불가능한 경우가 한 건이라도 있는 차량은 '대여불가능'이 출력되도록 해 준다.

• 정리) 한 대의 차량이라도 여러 개의 대여 기록 (혹은 대여 예정 기록) 이 있다. 이 기록들을 '2022-11-01 ~ 2022-11-30' 구간 기준으로, 대여가능 / 대여불가능 으로 구분하고, CAR_ID 별로 GROUP BY 후 MAX() 함수를 적용해주면, 한 건이라도 대여 불가능한 차량은 대여 여부 컬럼에 '대여불가능' 이라는 데이터를 출력하게 된다. (즉, 11월에 대여가 불가능한 차량은 '대여불가능', 대여가 가능한 차량은 '대여가능' 데이터를 출력한다.)

• HAVING 절에 대여 여부가 '대여가능' 인 차량 정보만 필터링한다.

• 위에까지의 테이블을 TEMP_01로 지정

• 이후 TEMP_01과 할인 테이블(CAR_RENTAL_COMPANY_DISCOUNT_PLAN)을 CAR_TYPE 기준으로 JOIN 한다.

• 여러 할인들 중, '30일 이상' 할인 정보만 추출하기 위해 WHERE절에 DURATION_TYPE = '30일 이상' 조건을 준다.

• 전체적인 요금을 계산해준다. DAILY_FEE에 DISCOUNT_RATE를 적용한 금액을 곱해주고, ROUND 함수를 이용해 정수부분만 출력해준다. 금액을 FEE 컬럼으로 출력해준다.

• ORDER BY를 통해 FEE 내림차순, CAR_TYPE, CAR_ID 순으로 정렬해준다.

• 위에까지의 테이블을 TEMP_02로 지정

• TEMP_02 테이블에서 FEE가 50000 ~ 2000000 사이의 값을 가지는 데이터만 출력하기 위해 WHERE절에서 해당 조건을 걸어준다.

• 🌟🌟🌟 차량정보 테이블과 차량대여기록 테이블은 1:M 관계를 가진다. 따라서 같은 차량이라도 여러 개의 대여 기록(대여 예정 기록) 이 있다. 이를 GROUP BY를 통해 '차량별' 데이터를 구해줘야 하는데, 11월 기간에 대여가 불가능한 차량을 찾기 위해 CASE WHEN을 통해 대여 여부를 구해주었고, GROUP BY 했을 때 대여여부가 '대여불가능'인 차량은 '대여불가능' 값이 대표값이 되도록 MAX() 함수를 사용해주었다.