[프로그래머스 SQL 코딩테스트 연습] Lv3. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기(MySQL)

🤔 문제

다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은 `CAR_RENTAL_COMPANY_RENTAL_HISTORY` 테이블입니다. `CAR_RENTAL_COMPANY_RENTAL_HISTORY` 테이블은 아래와 같은 구조로 되어있으며, `HISTORY_ID`, `CAR_ID`, `START_DATE`, `END_DATE` 는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.

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

 

 

예시

예를 들어 `CAR_RENTAL_COMPANY_RENTAL_HISTORY` 테이블이 다음과 같다면

 

2022년 10월 16일에 대여 중인 자동차는 자동차 ID가 1, 2인 자동차이고, 대여 가능한 자동차는 자동차 ID가 3, 4이므로, '대여중' 또는 '대여 가능' 을 표시하는 컬럼을 추가하고, 자동차 ID를 기준으로 내림차순 정렬하면 다음과 같이 나와야 합니다.

 

 

 

 

 

 

 

 

---

 

💻 나의 풀이

-- 대여 시작일이 가장 최근인 대여 기록 필터링
WITH CAR AS(
    SELECT 
        HISTORY_ID, 
        CAR_ID, 
        DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE,
        DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE (CAR_ID, START_DATE) IN (SELECT CAR_ID, MAX(START_DATE)
                                    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                                    GROUP BY CAR_ID)
)
SELECT 
    CAR_ID,
    CASE 
        WHEN (START_DATE < '2022-10-17' AND END_DATE > '2022-10-15') THEN '대여 중'
        ELSE '대여 가능'
    END AS AVAILIABILITY
FROM CAR
ORDER BY 1 DESC;

 

 

 

 

 

 

결과

 

 

 

 

 

 

 

---

🖍 오답노트

틀린 이유

 

• 대여 여부 확인 논리: `CASE ~ WHEN ~ THEN` 문에서 대여 여부를 확인하는 논리가 잘못되었다. `START_DATE`와 `END_DATE`의 비교가 제대로 되지 않아 '대여 중' 조건을 정확히 확인하지 못한다.
• 데이터 필터링 방법: `WITH` 절에서 `MAX(START_DATE)`를 사용하는 논리로 각 자동차의 가장 최근 대여 기록만 필터링하는 것은 잘못된 접근이고, 모든 기록을 확인해야한다.

 

 

레벨 3짜리 문제 치고는 꽤나 어려웠던 것 같다.

 

 

 

 

정답 풀이

1. CASE~WHEN으로 조건을 충족하는 날짜를 필터링한 후 MAX로 가장 최근 대여를 추출하는 풀이

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

 

• `MAX()` :
  • `CAR_ID`별로 존재하는 여러 기록들 중, 가장 최근에 대여가 종료된 기록에 대해서만 확인하기 위해 사용
  • `MAX()`를 사용하지 않으면 '대여중'인데도 '대여 가능'으로 나오기 때문에 반드시 써야 한다.

• '2022-10-16 BETWEEN DATE_FORMAT(START_DATE, '%Y-%m-%d') AND DATE_FORMAT(END_DATE, '%Y-%m-%d') :
  • `날짜 BETWEEN, A AND B` : A와 B를 모두 포함
• `GROUP BY CAR_ID` :
  • `CAR_ID`별로 기록

 

 

 

2. CASE WHEN안에 서브쿼리로 조건을 충족하는 CAR_ID를 필터링하는 풀이

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

• 서브쿼리를 사용해서 2022-10-16일이 대여 시작일과 대여 종료일 사이에 있는 `CAR_ID`를 구한다.
• `GROUP BY` 로 `CAR_ID` 별로 위에서 구한 조건에 해당하는 `CAR_ID` 라면 ‘대여중’으로, 그 외에는 ‘대여 가능’으로 표시하고 `AVAILABILITY` 라는 컬럼에 값을 담는다.

 

 

3. WITH절을 활용한 풀이

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

 

• CTE (CAR_STATUS):
  • 각 자동차의 대여 기록을 확인하여 2022-10-16이 START_DATE와 END_DATE 사이에 있는지 확인한다.
  • `CASE ~ WHEN ~ THEN` 문을 사용하여 해당 날짜에 대여 중인지 여부를 '대여중' 또는 '대여 가능'으로 표시
• 메인 쿼리:
  • `CAR_STATUS` CTE에서 `CAR_ID`별로 그룹화
  • `MAX(AVAILABILITY)`를 사용하여 각 자동차의 대여 상태를 집계한다. → 각 `CAR_ID`별로 '대여중'과 '대여 가능' 중 더 높은 값을 선택함 (여기서는 '대여중'이 '대여 가능'보다 높다고 가정)
  • `CAR_ID`를 기준으로 내림차순으로 정렬

 

 

 

 

 

---

🔗 References

[풀이1 참고]

https://velog.io/@greaceh/%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%A8%B8%EC%8A%A4-SQL-%EC%9E%90%EB%8F%99%EC%B0%A8-%EB%8C%80%EC%97%AC-%EA%B8%B0%EB%A1%9D%EC%97%90%EC%84%9C-%EB%8C%80%EC%97%AC%EC%A4%91-%EB%8C%80%EC%97%AC-%EA%B0%80%EB%8A%A5-%EC%97%AC%EB%B6%80-%EA%B5%AC%EB%B6%84%ED%95%98%EA%B8%B0

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

 

 

 

[풀이2 참고]

https://suminii.tistory.com/entry/MYSQL-%EC%9E%90%EB%8F%99%EC%B0%A8-%EB%8C%80%EC%97%AC-%EA%B8%B0%EB%A1%9D%EC%97%90%EC%84%9C-%EB%8C%80%EC%97%AC%EC%A4%91-%EB%8C%80%EC%97%AC-%EA%B0%80%EB%8A%A5-%EC%97%AC%EB%B6%80-%EA%B5%AC%EB%B6%84%ED%95%98%EA%B8%B0

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