[SQL/코드카타] 프로그래머스 - 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (NOT EXISTS)

문제

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

프로그래머스

---

✍️ 문제 요약

• 자동차 종류 : 세단 또는 SUV
• 2022-11-1 부터 2022-11-30 까지 대여 가능
• 30일간의 대여 금액이 50만원 이상 200만원 이하인 자동차
• 자동차 id, 자동차 종류, 대여 금액(컬럼:FEE) 출력
• 대여 금액 내림차순, 자동차 종류 오름차순, 자동차 id 내림차순

---

내가 작성한 코드

WITH carfees AS (
    SELECT 
        c.car_id,
        c.car_type,
        ROUND(c.daily_fee * (1 - p.discount_rate/100) * 30) as FEE
    FROM car_rental_company_car as c
    JOIN car_rental_company_discount_plan as p
      ON c.car_type = p.car_type
    WHERE c.car_type IN ('세단', 'SUV')
      AND p.duration_type = '30일 이상'
)

SELECT
    cf.car_id,
    cf.car_type,
    cf.FEE
FROM carfees as cf
WHERE FEE BETWEEN 500000 AND 2000000
  AND NOT EXISTS (
        SELECT 1
        FROM car_rental_company_rental_history as h
        WHERE h.car_id = cf.car_id           -- car_id가 같은 것 중에서
          AND h.start_date < '2022-11-01'  
          AND h.end_date > '2022-11-30'   
  )
ORDER BY FEE DESC, car_type ASC, car_id DESC;

오답이다.너무 어렵다... 큰일이다! 날짜 조건이 상당히 까다로운 문제이다. 

2022-11-1 부터 2022-11-30 까지 대여 가능하다?

• 우선, 11월 동안 대여 기록이 없어야 한다는 말
• 이 조건을 어떻게 걸어야 하는 거지? 범위로 표현해야 할 것 같은데, 논리가 조금 어렵다...
  • h.start_date < '2022-11-30' 이면서 h.end_date > '2022-11-01'  => 11월에 하루라도 대여 기록이 있는 것!!
  • 이해하기가 어렵긴 한데, 이렇게 생각해보기로 하자! 하루라도 11월에 대여 기록이 있으면 안돼!!
  • 그러니까 위에 조건에 해당하는 날짜를 제외시키자!! => NOT EXISTS 사용 (🚨주의 : 행을 불러오는게 아닌 참거짓 확인용)
  • 다시 쉽게 풀어서 설명하면,
    • 11월에 이미 대여를 시작한 차량들 => start_date
    • 11월에 아직 반납이 안된 차량들 => end_date
    • 합치면 : 11월에 이미 대여기록이 있는 애들
    • NOT EXISTS  (...) : 11월에 대여기록이 없는 애들!!

---

코드 설명

1. '세단' and 'SUV' 차량 | '30일 이상' | FEE 컬럼 생성('30일 이상' 대여시 할인율 적용) : WITH 문(CTE)에 담는다!!

WITH carfees AS (
    SELECT 
        c.car_id,
        c.car_type,
        ROUND(c.daily_fee * (1 - p.discount_rate/100) * 30) as FEE
    FROM car_rental_company_car as c
    JOIN car_rental_company_discount_plan as p
      ON c.car_type = p.car_type
    WHERE c.car_type IN ('세단', 'SUV')
      AND p.duration_type = '30일 이상'
)

2. 그 중 50만원 이상 200만원 이하인 FEE 의 조건을 주고,

WHERE FEE BETWEEN 500000 AND 2000000

3. NOT EXISTS 를 사용해서 '11월에 하루라도 대여기록 있는 차량들'이 아닌 행이 있는지(True) 확인 ⭐️⭐️⭐️⭐️⭐️

  AND NOT EXISTS (
        SELECT 1
        FROM car_rental_company_rental_history as h
        WHERE h.car_id = cf.car_id
          AND h.start_date < '2022-11-30'                             
          AND h.end_date > '2022-11-01'
          )

 3. 순서 정렬

ORDER BY FEE DESC, car_type ASC, car_id DESC;

---

최종 쿼리

• NOT EXISTS 를 활용한 풀이

WITH carfees AS (
    SELECT 
        c.car_id,
        c.car_type,
        ROUND(c.daily_fee * (1 - p.discount_rate/100) * 30) as FEE
    FROM car_rental_company_car as c
    JOIN car_rental_company_discount_plan as p
      ON c.car_type = p.car_type
    WHERE c.car_type IN ('세단', 'SUV')
      AND p.duration_type = '30일 이상'
)

SELECT
    cf.car_id,
    cf.car_type,
    cf.FEE
FROM carfees as cf
WHERE FEE BETWEEN 500000 AND 2000000
  AND NOT EXISTS (
        SELECT 1
        FROM car_rental_company_rental_history as h
        WHERE h.car_id = cf.car_id         
          AND h.start_date < '2022-11-30'
          AND h.end_date > '2022-11-01'
  )
ORDER BY FEE DESC, car_type ASC, car_id DESC;

---

🔍 또 다른 풀이

• MAX(조건)을 활용한 풀이
  • MySQL은 Boolean 값을 내부적으로 1(참)과 0(거짓)으로 처리

WITH CHECK_CAR_STATUS AS(
SELECT
    CAR_ID,
    MAX(START_DATE < "2022-11-30" AND END_DATE > "2022-11-01") AS STATUS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY CAR_ID)

SELECT
    *
FROM(
SELECT
    C1.CAR_ID,
    C1.CAR_TYPE,
    ROUND(DAILY_FEE * (1 - C3.DISCOUNT_RATE/100) * 30) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C1
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C3
ON C1.CAR_TYPE = C3.CAR_TYPE
WHERE
    C1.CAR_TYPE IN ('세단','SUV') AND 
    C1.CAR_ID IN (
        SELECT
            CAR_ID
        FROM CHECK_CAR_STATUS
        WHERE STATUS = 0) AND
    C3.DURATION_TYPE = "30일 이상") SUB
WHERE FEE BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC

 

---

틈새 문법

NOT EXISTS

• 주로 WHERE 절에서 서브쿼리와 함께 사용

SELECT column1, column2, ...
FROM table1
WHERE NOT EXISTS (
    SELECT 1
    FROM table2
    WHERE table1.column_name = table2.column_name
);

예시

Orders 테이블과 Customers 테이블이 있을 때, 주문이 없는 고객을 찾고 싶을 때:

• Orders 테이블에 존재하지 않는 고객의 customer_id를 서브쿼리로 조회합니다.
• Customers 테이블에서 해당 customer_id를 가진 고객을 NOT EXISTS 조건에 따라 조회합니다.

    SELECT c.customer_name
    FROM Customers c
    WHERE NOT EXISTS (
        SELECT 1
        FROM Orders o
        WHERE c.customer_id = o.customer_id
    );

이 쿼리는 Orders 테이블에 주문 기록이 없는 모든 고객의 이름을 반환한다.

✅ NOT EXISTS의 동작 방식

1. 메인 쿼리 실행: Customers 테이블에서 행을 하나씩 가져옵니다. 
    
2. 서브쿼리 실행: 해당 행을 기준으로 서브쿼리가 실행됩니다. 
    
3. 결과 확인:
   • 서브쿼리가 하나 이상의 행을 반환하면 NOT EXISTS는 False가 됩니다.
   • 서브쿼리가 어떤 행도 반환하지 않으면 NOT EXISTS는 True가 됩니다. 
      
4. 결과 출력: NOT EXISTS가 True인 경우에만 메인 쿼리의 해당 행이 결과에 포함됩니다.

 

✅ EXISTS와의 차이점

• EXISTS: 서브쿼리의 결과가 존재하면 True를 반환하여 메인 쿼리의 데이터를 선택합니다. 
• NOT EXISTS: 서브쿼리의 결과가 존재하지 않아야 True를 반환하여 메인 쿼리의 데이터를 선택합니다

끝.