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

문제

MYSQL

내가 작성한 정답

WITH A AS (
    SELECT CAR_ID,
        CASE
            WHEN START_DATE <= '2022-10-16' AND END_DATE >= '2022-10-16' THEN '대여중'
            ELSE '대여 가능'        
        END AS AVAILABILITY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)
SELECT CAR_ID, MAX(AVAILABILITY)
FROM A
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

다른 사람들이 작성한 정답

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

ORACLE

내가 작성한 정답

WITH A AS (
    SELECT CAR_ID,
        CASE
            WHEN TO_CHAR(START_DATE,'YYMMDD') <= '221016' AND TO_CHAR(END_DATE,'YYMMDD') >= '221016' THEN '대여중'
            ELSE '대여 가능'        
        END AS AVAILABILITY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)
SELECT CAR_ID, MAX(AVAILABILITY)
FROM A
GROUP BY CAR_ID
ORDER BY CAR_ID DESC