[SQL] Programmers 문제 풀기(특정 기간동안 대여 가능한 자동차들의 대여비용 구하기)

해당 문제는 Programmers에서 사용한 문제이며 모든 테이블의 자료와 출처는 Programmers임을 밝힙니다.

프로그래머스

해당 글에서는 WITH AS, JOIN, 서브쿼리를 이용하여 푸는 문제로 초보자가 하기에는 보통~어려움 정도의 난이도를 가지고 있는 것 같습니다.

해당 문제 원본을 확인 하시려면 해당 사이트를 들어가시면 될 것 같습니다.

 

시작하겠습니다.

---

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

이 문제를 볼 때 제가 위에 중요한 부분을 색칠해놨습니다. 그리고 각각의 테이블이 무엇을 의미하는지 잘 알고 있어야 합니다. (저는 테이블 제대로 생각안하고 풀어가지고 너무 바보 같은 행동을 했답니다....)

 

제가 생각하기에는 CAR_RENTAL_COMPANY_RENTAL_HISTORY 해당 테이블의 의미가 중요한 것 같습니다. 해당 테이블은 자동차를 대여한 날짜 기록들이 저장 되어 있는 테이블입니다. 

즉, 해당 데이터 날짜에 기록된 자동차 ID는 그 날짜에 대여가 되어 대여를 할 수 없는 것입니다.

그러므로 저희는 2022-11-01 ~ 2022-11-31일 사이 날짜에 대여를 안한 자동차만 사용할 수 있다는 것이지요!!

 

위의 제가 설명한 조건을 잘 생각하고 푸시면 나머지는 잘 생각하고 풀면 복잡하지만 풀릴 것입니다.

 

1. 조건 날짜에 대여한 자동차 CAR_ID 알아내기

SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE <= '2022-11-30' AND
      END_DATE >='2022-11-01'

해당 코드를 쉽게 말하자면 대여 시작 날짜 또는 종료 날짜가 2022-11-01일에서 2022-11-30일 사이에 있으면 안됩니다.

그러므로 START_DATE가 2022-11-01일 이전 부터 END_DATE는 2022-11-01일 이후에 있는 것이 있으면 안되는 것과 같은 의미입니다. 그리고 START_DATE가 조건의 날짜 중간에 있으면 안되기도 하구요!!

코드 결과

위와 같이 날짜 들이 조건의 날짜와 겹치는 경우들이 보이는 CAR_ID는 저희가 제외 시켜야 합니다.

---

2. 위에서 찾아낸 CAR_ID 제외 시킨 후 조건에 맞는 값들 추출

WITH CTE AS (SELECT a.CAR_ID, a.CAR_TYPE, a.DAILY_FEE
FROM CAR_RENTAL_COMPANY_CAR a
        JOIN
     CAR_RENTAL_COMPANY_RENTAL_HISTORY b ON a.CAR_ID = b.CAR_ID
WHERE a.CAR_ID NOT IN (SELECT CAR_ID
          			   FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
         			   WHERE START_DATE <= '2022-11-30' AND
                			 END_DATE >='2022-11-01') AND
a.CAR_TYPE IN ('세단', 'SUV')
)

코드 결과

저희는 JOIN을 이용하여 대여 날짜에 가능한 CAR_ID만 가지고 있으며 문제에서 세단과 SUV만 찾아내라하여 조건을 추가해주었습니다.

그리고 가독성을 위해 저는 WITH AS를 사용하여 CTE라는 새로운 테이블을 따로 만들어 주었습니다.

 

이제 계산을 하여 FEE 값을 위해 할인률을 찾아내어 계산을 해주면 될 듯 합니다.

---

3. 중복된 CAR_ID 제거 후 FEE 값 계산하기

SELECT distinct a.CAR_ID
     , a.CAR_TYPE
     , ROUND((a.DAILY_FEE - (a.DAILY_FEE * (discount_rate / 100))) * 30) AS FEE
FROM CTE a
    JOIN
     CAR_RENTAL_COMPANY_DISCOUNT_PLAN b ON a.CAR_TYPE = b.CAR_TYPE
WHERE b.duration_type = '30일 이상'

코드 결과

2번에서 설명했던 이미지를 보면 중복되는 CAR_ID가 있으므로 DISTINCT로 중복을 제거해줍니다.

 

문제에서 모든 할인율은 각 CAR_TYPE에 맞고 DURATION_TYPE은 30일 이상의 할인율 값을 이용하라 했기 때문에 WHERE 절에 해당 조건을 추가 해주었습니다.

그리고 할인율을 적용한 값들을 계산하여 위의 이미지와 같이 FEE값을 구해주었습니다.

 

마지막으로 가독성을 편하게 하기위해 WITH AS를 사용하여 CTE2라는 새로운 테이블을 만들어 주었습니다.

 

이제 마지막 조건인 500000원 이상 2000000원 미만의 값만 가져오면 될 것 같습니다.

---

4. 조건에 맞게 설정해주고 답 구하기

WITH CTE AS (SELECT a.CAR_ID, a.CAR_TYPE, a.DAILY_FEE
FROM CAR_RENTAL_COMPANY_CAR a
        JOIN
     CAR_RENTAL_COMPANY_RENTAL_HISTORY b ON a.CAR_ID = b.CAR_ID
WHERE a.CAR_ID NOT IN (SELECT CAR_ID
          FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
          WHERE START_DATE <= '2022-11-30' AND
                END_DATE >='2022-11-01') AND
    a.CAR_TYPE IN ('세단', 'SUV')
),
CTE2 AS (SELECT distinct a.CAR_ID
     , a.CAR_TYPE
     , ROUND((a.DAILY_FEE - (a.DAILY_FEE * (discount_rate / 100))) * 30) AS FEE
FROM CTE a
    JOIN
     CAR_RENTAL_COMPANY_DISCOUNT_PLAN b ON a.CAR_TYPE = b.CAR_TYPE
WHERE b.duration_type = '30일 이상')

SELECT *
FROM CTE2
WHERE FEE >= 500000 and FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC

이제 CTE2를 이용하고 WHERE절을 통해 500000원 이상 2000000원 미만의 값만 가져오도록 만들어 주었습니다. 

 

조건에 맞게 정렬해주면 해당 코드는 정답이 됩니다!!!

---

이상으로 Programmers 문제 풀이를 마치도록 하겠습니다.