🚀[SQL 알고리즘 문제 풀이] Programmers : 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기(JOIN)

👇특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

프로그래머스

 

🔓 문제 설명

자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요.

결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

• 핵심조건
  1. 자동차 종류 필터링 (세단, SUV)
  2. 11월 1일 ~ 30일 사이의 모든 대여 기록 제외
  3. 30일 대여 요금 계산 (할인율 적용) 및 정수 출력

 

예시 문제 데이터

[CAR_RENTAL_COMPANY_CAR]

CAR_ID	CAR_TYPE	DAILY_FEE	OPTIONS
1	SUV	25000	가죽시트,열선시트,후방카메라
2	세단	14000	스마트키,네비게이션,열선시트
3	트럭	32000	주차감지센서,후방카메라,가죽시트
4	세단	12000	열선시트,후방카메라
5	세단	22000	스마트키,주차감지센서

 

[CAR_RENTAL_COMPANY_RENTAL_HISTORY]

HISTORY_ID	CAR_ID	START_DATE	END_DATE
1	1	2022-08-27	2022-09-02
2	1	2022-10-03	2022-10-04
3	2	2022-10-05	2022-10-20
4	2	2022-10-10	2022-11-12
5	3	2022-10-16	2022-10-17

 

[CAR_RENTAL_COMPANY_DISCOUNT_PLAN]

PLAN_ID	CAR_TYPE	DURATION_TYPE	DISCOUNT_RATE
1	트럭	7일 이상	5%
2	트럭	30일 이상	7%
3	트럭	90일 이상	10%
4	세단	7일 이상	5%
5	세단	30일 이상	10%
6	세단	90일 이상	15%
7	SUV	7일 이상	3%
8	SUV	30일 이상	8%
9	SUV	90일 이상	12%

---

⚡SQL Query

내가 작성한 답

WITH COMPANY_CAR_FEE AS (
    SELECT CC.CAR_ID,
        CC.CAR_TYPE,
        ((CC.DAILY_FEE*30)
            * (1 - DP.DISCOUNT_RATE/100)) AS FEE
    FROM CAR_RENTAL_COMPANY_CAR CC JOIN  CAR_RENTAL_COMPANY_DISCOUNT_PLAN DP
    ON CC.CAR_TYPE = DP.CAR_TYPE
    WHERE CC.CAR_TYPE IN ('세단', 'SUV')
        AND DP.DURATION_TYPE = '30일 이상'
), RENTAL_HISTORY AS (
    SELECT CAR_ID, START_DATE, END_DATE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY RH1
    WHERE NOT EXISTS (
        SELECT 1
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY RH2
        WHERE RH1.CAR_ID = RH2.CAR_ID
            AND END_DATE >= TO_DATE('2022-11-01', 'YYYY-MM-DD')
            AND START_DATE <= TO_DATE('2022-11-30', 'YYYY-MM-DD')
    )
)

SELECT DISTINCT CF.CAR_ID, CF.CAR_TYPE, CF.FEE
FROM COMPANY_CAR_FEE CF JOIN RENTAL_HISTORY RH
    ON CF.CAR_ID = RH.CAR_ID
WHERE CF.FEE BETWEEN 500000 AND 1999999
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC

 

추천 답안

SELECT 
    C.CAR_ID, 
    C.CAR_TYPE, 
    FLOOR(C.DAILY_FEE * 30 * (1 - P.DISCOUNT_RATE/100)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P 
    ON C.CAR_TYPE = P.CAR_TYPE 
    AND P.DURATION_TYPE = '30일 이상'
WHERE C.CAR_TYPE IN ('세단', 'SUV')
  AND NOT EXISTS (
      SELECT 1 
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H 
      WHERE H.CAR_ID = C.CAR_ID
        AND H.END_DATE >= TO_DATE('2022-11-01', 'YYYY-MM-DD')
        AND H.START_DATE <= TO_DATE('2022-11-30', 'YYYY-MM-DD')
  )
  AND FLOOR(C.DAILY_FEE * 30 * (1 - P.DISCOUNT_RATE/100)) BETWEEN 500000 AND 1999999
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;

 

---

🔎 풀이 설명

1. 11월 대여 가능 자동차 추출

• START_DATE와 END_DATE가 11월 1일 ~ 30일 사이에 단 하루라도 겹치는 기록이 있다면 후보에서 탈락시킵니다.
• NOT()을 사용하면 같은 CAR_ID가 중복으로 나오니 때문에 NOT EXISTS()를 사용

2. 30일간 대여 금액 계산

• 계산식: DAILY_FEE * 30 * (1 - DISCOUNT_RATE / 100)

---

💡인사이트

1. NOT() vs NOT EXISTS()

구분	NOT()	NOT EXISTS()
논리 단위	행(Row) 단위 필터링	집합(Set) 단위 필터링
결과 형태	기록 개수만큼 중복 발생 가능	자동차당 1건으로 유일함
정확도	과거 기록이 있으면 오답 발생 위험	어떤 기록이든 겹치면 탈락시켜 정확함
성능	모든 이력을 조인하고 정렬해야 함	나쁜 기록 찾는 순간 멈춤 (Early Exit)

• NOT(): 모든 행을 하나하나 검사하여 조건에 맞는 이력(Row)의 개수만큼 CAR_ID가 중복 출력됩니다.
• NOT EXISTS(): 각 행에 대해 존재 여부만 확인하여 나쁜 기록을 하나라도 찾는 즉시 탐색을 멈추고 데이터를 유일하게 한 번만 출력됩니다.

 

2. 헷갈리는 날짜 교차 비교

'나의 시작일' <= '상대의 종료일' AND '나의 종료일' >= '상대의 시작일'

• 이 공식을 사용하면 시작일에 걸친 경우와 종료일에 걸친 경우, 기간 내에 포함된 경우를 모두 잡아낼 수 있습니다.