[LV.5] JOIN > 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 ⭐️

문제

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

프로그래머스

---

문제 설명

• 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를 기준으로 내림차순 정렬

---

정답 코드

WITH DISCOUNT_PLAN AS (SELECT *
                       FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                       WHERE DURATION_TYPE = '30일 이상'),
RENTED_NOV AS (SELECT DISTINCT CAR_ID
               FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
               WHERE START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01'),
FEE_CALC AS (SELECT C.CAR_ID,
                    C.CAR_TYPE,
                    FLOOR((C.DAILY_FEE * (1 - D.DISCOUNT_RATE / 100.0)) * 30) AS FEE
             FROM CAR_RENTAL_COMPANY_CAR C JOIN DISCOUNT_PLAN D ON C.CAR_TYPE = D.CAR_TYPE
             WHERE C.CAR_TYPE IN ('세단', 'SUV'))

SELECT F.CAR_ID, F.CAR_TYPE, F.FEE
FROM FEE_CALC F
WHERE NOT EXISTS (SELECT 1
                  FROM RENTED_NOV R
                  WHERE R.CAR_ID = F.CAR_ID)
      AND F.FEE BETWEEN 500000 AND 1999999
ORDER BY F.FEE DESC, F.CAR_TYPE, F.CAR_ID DESC

---

코드 설명

1. 'DISCOUNT_PLAN' CTE

WITH DISCOUNT_PLAN AS (
    SELECT *
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE DURATION_TYPE = '30일 이상'
)

• CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서
• 대여 기간이 30일 이상인 할인 계획만 골라서 임시 테이블(CTE)로 만들기
• 이후 할인율 정보를 이 테이블에서 가져올 예정

2. 'RENTED_NOV' CTE

RENTED_NOV AS (
    SELECT DISTINCT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01'
)

• CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서
• 2022년 11월 기간(11월 1일부터 11월 30일까지)과 겹치는 대여 기록이 있는 자동차 ID를 중복 없이 추출
• 즉, 11월에 대여된 차량 목록

3. 'FEE_CALC' CTE

FEE_CALC AS (
    SELECT C.CAR_ID,
           C.CAR_TYPE,
           FLOOR((C.DAILY_FEE * (1 - D.DISCOUNT_RATE / 100.0)) * 30) AS FEE
    FROM CAR_RENTAL_COMPANY_CAR C JOIN DISCOUNT_PLAN D ON C.CAR_TYPE = D.CAR_TYPE
    WHERE C.CAR_TYPE IN ('세단', 'SUV')
)

• CAR_RENTAL_COMPANY_CAR에서 세단과 SUV 차량을 대상으로
• 각 차량의 할인 적용된 일일 요금을 구함:
  • C.DAILY_FEE * (1 - D.DISCOUNT_RATE / 100.0) → 할인 적용 후 일일 요금
  • 여기에 30일을 곱해 한 달(30일 기준) 요금 계산
  • FLOOR 함수로 소수점 내림 처리
• 할인 플랜과 차량 유형을 기준으로 조인해서 할인율을 적용

4. 메인 쿼리

SELECT F.CAR_ID, F.CAR_TYPE, F.FEE
FROM FEE_CALC F
WHERE NOT EXISTS (
        SELECT 1
        FROM RENTED_NOV R
        WHERE R.CAR_ID = F.CAR_ID
      )
      AND F.FEE BETWEEN 500000 AND 1999999
ORDER BY F.FEE DESC, F.CAR_TYPE, F.CAR_ID DESC

* WHERE F.CAR_ID NOT IN (SELECT CAR_ID FROM RENTED_NOV) AND ~ 도 가능

• FEE_CALC에서 계산된 차량별 월 요금 정보를 사용
• NOT EXISTS 조건으로 11월에 대여된 차량은 제외
  • 즉, RENTED_NOV에 포함되지 않은 차량만 선택
• 월 요금이 50만 이상, 200만 미만인 차량만 필터링
• 결과를 월 요금 내림차순, 차량 종류 오름차순, 차량 ID 내림차순으로 정렬하여 출력

---

NOT EXISTS 기본 구조

SELECT 컬럼들
FROM 메인테이블 M
WHERE NOT EXISTS (
    SELECT 1
    FROM 서브테이블 S
    WHERE S.조건 = M.조건
)

• 메인테이블 M의 각 행에 대해
  서브쿼리에서 M과 관련된 조건을 만족하는 행이 존재하지 않을 때만 결과에 포함
• 왜 SELECT 1을 쓰는가?
  • SELECT 1은 존재 여부만 확인하기 위한 간단한 표현 
  • EXISTS나 NOT EXISTS 서브w쿼리 안에서 실제로 어떤 컬럼을 선택하느냐는 결과에 영향이 없음

---

첫 번째 시도 (실패)

WITH DISCOUNT_PLAN AS (SELECT *
               FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
               WHERE DURATION_TYPE = '30일 이상')

SELECT C.CAR_ID, C.CAR_TYPE, FLOOR((C.DAILY_FEE * (1-D.DISCOUNT_RATE/100)) * 30) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
     JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID = H.CAR_ID
     JOIN DISCOUNT_PLAN D ON C.CAR_TYPE = D.CAR_TYPE
WHERE C.CAR_TYPE IN ('세단', 'SUV') AND
      (H.START_DATE < '2022-11-01' OR H.START_DATE > '2022-11-30') AND
      (H.END_DATE < '2022-11-01' OR H.END_DATE > '2022-11-30') AND
      500000 <= (C.DAILY_FEE * (1-D.DISCOUNT_RATE/100)) * 30 AND
      (C.DAILY_FEE * (1-D.DISCOUNT_RATE/100)) * 30 < 2000000
ORDER BY FEE DESC, C.CAR_TYPE, C.CAR_ID DESC

---

문제점

• 자동차가 2022년 11월 전체에 '대여 불가'한 경우를 제외해야 하는데, 지금 조건은 '1건이라도 대여 기록이 11월과 겹치지 않으면 포함'하고 있음
• 중복 대여 기록으로 인해 JOIN 시 자동차 한 대가 여러 번 나올 수 있음

---

두 번째 시도 (실패)

WITH DISCOUNT_PLAN AS (SELECT *
                       FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                       WHERE DURATION_TYPE = '30일 이상'),
RENTED_NOV AS (SELECT DISTINCT CAR_ID
               FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
               WHERE START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01')

SELECT C.CAR_ID, C.CAR_TYPE, FLOOR((C.DAILY_FEE * (1-D.DISCOUNT_RATE/100)) * 30) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
     JOIN RENTED_NOV R ON C.CAR_ID = R.CAR_ID
     JOIN DISCOUNT_PLAN D ON C.CAR_TYPE = D.CAR_TYPE
WHERE C.CAR_TYPE IN ('세단', 'SUV') AND
      C.CAR_ID NOT IN (SELECT CAR_ID FROM RENTED_NOV) AND
      500000 <= (C.DAILY_FEE * (1-D.DISCOUNT_RATE/100)) * 30 AND
      (C.DAILY_FEE * (1-D.DISCOUNT_RATE/100)) * 30 < 2000000
GROUP BY C.CAR_ID, C.CAR_TYPE
ORDER BY FEE DESC, C.CAR_TYPE, C.CAR_ID DESC

• 중복 발생할 일이 없는, 지금 상황에서는 GROUP BY 필요 없음

---

문제점

JOIN RENTED_NOV R ON C.CAR_ID = R.CAR_ID
...
WHERE C.CAR_ID NOT IN (SELECT CAR_ID FROM RENTED_NOV)

• 같은 테이블을 JOIN 해서 포함시켜놓고, 다시 WHERE에서 그 값을 제외하는 구조
  → 자기모순 (논리 충돌) 발생

---

세 번째 시도 (성공. 개선 필요)

WITH DISCOUNT_PLAN AS (SELECT *
                       FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                       WHERE DURATION_TYPE = '30일 이상'),
RENTED_NOV AS (SELECT DISTINCT CAR_ID
               FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
               WHERE START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01')

SELECT C.CAR_ID, C.CAR_TYPE, FLOOR((C.DAILY_FEE * (1-D.DISCOUNT_RATE/100)) * 30) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
     JOIN DISCOUNT_PLAN D ON C.CAR_TYPE = D.CAR_TYPE
WHERE C.CAR_TYPE IN ('세단', 'SUV') AND
      C.CAR_ID NOT IN (SELECT CAR_ID FROM RENTED_NOV) AND
      500000 <= (C.DAILY_FEE * (1-D.DISCOUNT_RATE/100)) * 30 AND
      (C.DAILY_FEE * (1-D.DISCOUNT_RATE/100)) * 30 < 2000000
ORDER BY FEE DESC, C.CAR_TYPE, C.CAR_ID DESC

---

개선점

• (C.DAILY_FEE * (1-D.DISCOUNT_RATE/100)) * 30 → 중복 계산 줄이기
• NOT IN은 NULL 때문에 예외가 발생할 수 있어, 더 안전한 NOT EXISTS 사용 권장
• DISCOUNT_RATE가 실수일 가능성을 대비해 '1 - (D.DISCOUNT_RATE / 100.0)'처럼 형변환을 명시하면 계산 오류 방지