SQL) 자동차 장기/단기 대여 구분하기

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

프로그래머스

 

문제

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성해주세요. 결과는 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

---

예시

 

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

HISTORY_ID	CAR_ID	START_DATE	END_DATE
1	4	2022-09-27	2022-11-27
2	3	2022-10-03	2022-11-04
3	2	2022-09-05	2022-09-05
4	1	2022-09-01	2022-09-30
5	3	2022-09-16	2022-10-15

2022년 9월의 대여 기록 중 '장기 대여' 에 해당하는 기록은 대여 기록 ID가 1, 4인 기록이고, '단기 대여' 에 해당하는 기록은 대여 기록 ID가 3, 5 인 기록이므로 대여 기록 ID를 기준으로 내림차순 정렬하면 다음과 같이 나와야 합니다.

HISTORY_ID	CAR_ID	START_DATE	END_DATE	RENT_TYPE
5	3	2022-09-16	2022-10-13	단기 대여
4	1	2022-09-01	2022-09-30	장기 대여
3	2	2022-09-05	2022-09-05	단기 대여
1	4	2022-09-27	2022-10-26	장기 대여

 

 

대여 시작 날짜와 반납 날짜의 차이를 계산하기 위해 TIMESTAMPDIFF함수를 사용했다. 

TIMESTAMPDIFF(단위, 날짜1, 날짜2)

단위

• SECOND : 초
• MINUTE : 분
• HOUR : 시
• DAY : 일
• WEEK : 주
• MONTH : 월
• QUARTER : 분기
• YEAR : 연

날짜1과 날짜2의 차이를 정해진 단위에 맞게 출력하는 함수이다.  이를 이용해 날짜 차이가 잘 계산되나 확인을 해보았다.

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,
CASE WHEN 
TIMESTAMPDIFF(DAY,DATE_FORMAT(START_DATE,'%Y-%m-%d'),DATE_FORMAT(END_DATE,'%Y-%m-%d')) >=30 THEN '장기 대여'
ELSE '단기 대여' END AS RENT_TYPE,TIMESTAMPDIFF(DAY,DATE_FORMAT(START_DATE,'%Y-%m-%d'),DATE_FORMAT(END_DATE,'%Y-%m-%d')) AS TIMESTAMPDIFF FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE DATE_FORMAT(START_DATE,'%Y-%m') ='2022-09'
ORDER BY HISTORY_ID DESC

 

HISTORY_ID	CAR_ID	START_DATE	END_DATE	RENT_TYPE	TIMESTAMPDIFF
669	25	2022-09-29	2022-10-28	단기 대여	29
668	13	2022-09-28	2022-09-28	단기 대여	0
667	10	2022-09-28	2022-10-12	단기 대여	14
665	23	2022-09-26	2022-10-06	단기 대여	10

 

장기,단기 대여를 구분하는 RENT_TYPE열은 CASE WHEN을 이용해 구하였다. TIMESTAMPDIFF의 값이 30일 이상인 경우 장기 대여로 구분되는 것을 비교해서 확인하였다. 하지만 정답이 아니였다. 

 

HISTORY_ID	CAR_ID	START_DATE	END_DATE	RENT_TYPE
5	3	2022-09-16	2022-10-13	단기 대여
4	1	2022-09-01	2022-09-30	장기 대여

결과 예시를 자세히 보면 CAR_ID가 1인 행은 9월 1일부터 30일 까지 대여를 했는데 장기 대여로 구분되어 있다. TIMESTAMPDIFF를 이용해 구하면 29가 나오지만 실질적인 대여기간은 시작날짜도 포함이다. 따라서 TIMESTAMPDIFF에 +1을 해주어야한다.

 

정답

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,
CASE WHEN 
TIMESTAMPDIFF(DAY,DATE_FORMAT(START_DATE,'%Y-%m-%d'),DATE_FORMAT(END_DATE,'%Y-%m-%d'))+1 >=30 THEN '장기 대여'
ELSE '단기 대여' END AS RENT_TYPE FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE DATE_FORMAT(START_DATE,'%Y-%m') ='2022-09'
ORDER BY HISTORY_ID DESC