[프로그래머스][Oracle][Level 3] 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

문제

CAR_RENTAL_COMPANY_RENTAL_HISTORY  테이블

Column name	Type	Nullable	Description
HISTORY_ID	INTEGER	FALSE	대여 기록 ID
CAR_ID	INTEGER	FALSE	자동차 ID
START_DATE	DATE	FALSE	대여 시작일
END_DATE	DATE	FALSE	대여 종료일

 

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

---

접근방법

1. 자동차는 여러 번 대여할 수 있다.

           → CAR_ID는 중복될 수 있다.   

 

2. 자동차를 대여 날짜 기간은 중복될 수 없다.

           → 하나의 자동차 기록에서 마지막으로 대여한 기록을 찾아 대여 유무를 판단한다. 

 

3. 위 2번 사항에 해당되지 않는 모든 자동차는 '대여 가능'으로 표시한다. 

---

해결

1. CAR_ID는 중복될 수 있다.   &   자동차를 대여 날짜 기간은 중복될 수 없다.

           → 자동차 ID와 대여 시작/종료 컬럼을 GROUP BY한다..

           → 대여 시작일이 '2022-10-16' 이전이고 대여 종료일이 '2022-10-16' 이후를 만족하는 행을 찾는다.

 

2. 위 2번 사항에 해당되지 않는 모든 자동차는 '대여 가능'으로 표시한다. 

 

→  CTE 및 UNION, GROUP BY 활용.

WITH AVAIL_CTE AS (
    SELECT CAR_ID, '대여중' AS AVAILABILITY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    GROUP BY CAR_ID, START_DATE, END_DATE
    HAVING START_DATE <= TO_DATE('2022-10-16', 'YYYY-MM-DD')
    AND END_DATE >= TO_DATE('2022-10-16', 'YYYY-MM-DD')
) SELECT * FROM AVAIL_CTE
UNION
SELECT CAR_ID, '대여 가능'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID NOT IN (SELECT CAR_ID FROM AVAIL_CTE)
ORDER BY 1 DESC