[SQL] 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

📍 문제

다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블입니다.

HISTORY_ID - 자동차 대여 기록 ID
CAR_ID - 자동차 ID
START_DATE - 대여 시작일
END_DATE - 대여 종료일


CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서
2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요.
이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

분명 CASE문 작성하는 것까지는 알고 있었는데 내가 생각한 대로 작성하니 틀렸다기에...

SELECT
    CAR_ID,
    (CASE 
        WHEN END_DATE < '2022-10-16' THEN '대여 가능'
        ELSE '대여중'
    END) AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

뭐 대충 이렇게 작성했었다.
이렇게 되면 답은 도출되는데,

🚨 첫 번째 문제점 = GROUP BY
하나의 CAR_ID에 대여 기록이 여러 개라 어떤 기록을 기준점으로 보아야 하는지 모르게 된다.

🚨 두 번째 문제점 = START_DATE 지정하지 않음
그러게 되면 END_DATE가 2022-10-16보다 큰 값이라 '대여중'이라고 구분되었는데, 만약 해당 데이터의 START_DATE가 2022-11-10 처럼 2022-10-16이 포함되지 않은 날짜라 '대여 가능'으로 표기되어야 한다면? 표기 오류가 드러난다.

그래서 SubQuery를 이용해 2022-10-16가 START_DATE와 END_DATE 사이에 존재하는 CAR_ID를 먼저 선택하고,
해당 날짜에 이미 빌리고 있는 사람이 있다는 거니까 '대여중'을 표기한다.

만약 해당 날짜 사이 존재하지 않는다면 이미 대여를 끝내고 대여되길 기다리고 있는 거니까 '대여 가능'으로 표기해 주어야 한다.

SELECT
    CAR_ID,
    (CASE 
        WHEN CAR_ID IN (
            SELECT CAR_ID
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
            WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE) THEN '대여중'
        ELSE '대여 가능'
    END) AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

서브쿼리 니 딱대. 내가 너때문에 이골이 난 지 벌써 삼십년째다...