Programmers - 자동차 평균 대여 기간 구하기

문제 설명

 

 

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

Column name	Type	Nullable
HISTORY_ID	INTEGER	FALSE
CAR_ID	INTEGER	FALSE
START_DATE	DATE	FALSE
END_DATE	DATE	FALSE

---

문제

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력하는 SQL문을 작성해주세요. 평균 대여 기간은 소수점 두번째 자리에서 반올림하고, 결과는 평균 대여 기간을 기준으로 내림차순 정렬해주시고, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬해주세요.

---

예시

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

HISTORY_ID	CAR_ID	START_DATE	END_DATE
1	1	2022-09-27	2022-10-01
2	1	2022-10-03	2022-11-04
3	2	2022-09-05	2022-09-05
4	2	2022-09-08	2022-09-10
5	3	2022-09-16	2022-10-15
6	1	2022-11-07	2022-12-06

자동차 별 평균 대여 기간은

• 자동차 ID가 1인 자동차의 경우, 대여 기간이 각각 5일, 33일, 30일인 기록이 존재하므로 평균 22.7일
• 자동차 ID가 2인 자동차의 경우, 대여 기간이 각각 1일, 3일인 기록이 존재하므로 평균 2일
• 자동차 ID가 3인 자동차의 경우, 대여 기간이 30일인 기록만 존재하므로 평균 30일 입니다. 평균 대여 기간이 7일 이상인 자동차는 자동차 ID가 1, 3인 자동차이고, 평균 대여 기간 내림차순 및 자동차 ID를 기준으로 내림차순 정렬하면 다음과 같이 나와야 합니다.

CAR_ID	AVERAGE_DURATION
3	30.0
1	22.7

---

정답

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING average_duration >= 7
ORDER BY average_duration DESC, car_id DESC;



SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(DATEDIFF(END_DATE, START_DATE)+1) >= 7
ORDER BY 2 DESC, 1 DESC;

 

 

첫번째 오답

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE)), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(DATEDIFF(END_DATE, START_DATE)) >= 7
ORDER BY 2 DESC, 1 DESC;

위와같이 쿼리를 입력한다면 대여기간이 제대로 계산되지 않는다. 동일한 날짜에 대여하여 반납하여도 내여기간은 1일로 계산이 되어야하는데 날짜가 동일한경우는 0일이 되어버리기 때문이다.

 

두번째 오답

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(DATEDIFF(END_DATE, START_DATE)) >= 7
ORDER BY 2 DESC, 1 DESC;

MYSQL에서는 별칭을 HAVING절에서 사용이 가능하다. SELECT에서 대여기간의 문제점을 파악하고 DATEDIFF값에서 +1을 하여 대여기간을 맞춰줬지만 HAVING절에서도 DATEDIFF를 직접 사용하고 있다. HAVING절에서 직접 사용하였기 때문에 +1이 제대로 적용되지 않은 상황이였다.