자동차 대여 기록 별 대여 금액 구하기

난이도 구분 : Level 4

문제

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

• 문제 출처 : https://school.programmers.co.kr/learn/courses/30/lessons/151141

문제 풀이

1. CAR_RENTAL_COMPANY_CAR, CAR_RENTAL_COMPANY_RENTAL_HISTORY, CAR_RENTAL_COMPANY_DISCOUNT_PLAN 3중 조인하기

SQL

SELECT *
FROM CAR_RENTAL_COMPANY_CAR A
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B ON A.CAR_ID = B.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C ON A.CAR_TYPE = C.CAR_TYPE

결과

2. 자동차 종류가 '트럭'인 자동차의 대여 기록 조회

SQL

SELECT *
FROM CAR_RENTAL_COMPANY_CAR A
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B ON A.CAR_ID = B.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C ON A.CAR_TYPE = C.CAR_TYPE
WHERE A.CAR_TYPE = '트럭'

결과

3. 기록 별로 대여 금액(컬럼명: FEE)을 구하기 + 대여 기록 ID와 대여 금액 리스트를 출력

SQL

SELECT DISTINCT B.HISTORY_ID, (CASE
WHEN DATEDIFF(B.END_DATE, B.START_DATE) + 1 < 7 
THEN ROUND(A.DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1))
WHEN DATEDIFF(B.END_DATE, B.START_DATE) + 1 BETWEEN 7 AND 30 
THEN ROUND(A.DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1) * (1 - (SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE = '7일 이상' AND CAR_TYPE = '트럭')/100))
WHEN DATEDIFF(B.END_DATE, B.START_DATE) + 1 BETWEEN 30 AND 90 
THEN ROUND(A.DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1) * (1 - (SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE = '30일 이상' AND CAR_TYPE = '트럭')/100))
ELSE ROUND(A.DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1) * (1 - (SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE = '90일 이상' AND CAR_TYPE = '트럭')/100))
END) AS FEE
FROM CAR_RENTAL_COMPANY_CAR A
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B ON A.CAR_ID = B.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C ON A.CAR_TYPE = C.CAR_TYPE
WHERE A.CAR_TYPE = '트럭'

결과

4. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬

SQL

SELECT DISTINCT B.HISTORY_ID, (CASE
WHEN DATEDIFF(B.END_DATE, B.START_DATE) + 1 < 7 
THEN ROUND(A.DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1))
WHEN DATEDIFF(B.END_DATE, B.START_DATE) + 1 BETWEEN 7 AND 30 
THEN ROUND(A.DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1) * (1 - (SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE = '7일 이상' AND CAR_TYPE = '트럭')/100))
WHEN DATEDIFF(B.END_DATE, B.START_DATE) + 1 BETWEEN 30 AND 90 
THEN ROUND(A.DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1) * (1 - (SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE = '30일 이상' AND CAR_TYPE = '트럭')/100))
ELSE ROUND(A.DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1) * (1 - (SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE = '90일 이상' AND CAR_TYPE = '트럭')/100))
END) AS FEE
FROM CAR_RENTAL_COMPANY_CAR A
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B ON A.CAR_ID = B.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C ON A.CAR_TYPE = C.CAR_TYPE
WHERE A.CAR_TYPE = '트럭'
ORDER BY FEE DESC, B.HISTORY_ID DESC;

결과

정답 SQL

SELECT DISTINCT B.HISTORY_ID, (CASE
WHEN DATEDIFF(B.END_DATE, B.START_DATE) + 1 < 7 
THEN ROUND(A.DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1))
WHEN DATEDIFF(B.END_DATE, B.START_DATE) + 1 BETWEEN 7 AND 30 
THEN ROUND(A.DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1) * (1 - (SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE = '7일 이상' AND CAR_TYPE = '트럭')/100))
WHEN DATEDIFF(B.END_DATE, B.START_DATE) + 1 BETWEEN 30 AND 90 
THEN ROUND(A.DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1) * (1 - (SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE = '30일 이상' AND CAR_TYPE = '트럭')/100))
ELSE ROUND(A.DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1) * (1 - (SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE = '90일 이상' AND CAR_TYPE = '트럭')/100))
END) AS FEE
FROM CAR_RENTAL_COMPANY_CAR A
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B ON A.CAR_ID = B.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C ON A.CAR_TYPE = C.CAR_TYPE
WHERE A.CAR_TYPE = '트럭'
ORDER BY FEE DESC, B.HISTORY_ID DESC;

풀이 과정에서 기억할 점

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 정보에 대하여

처음엔 위의 테이블에서 어떤 차 종류에 대해선 7일 이상, 30일 이상에 대해서만 정보가 있고, 90일 이상 조건에 대한 별다른 칼럼이 없다면 어떻게 처리해야 하는가에 대한 생각이 들었다.
그렇기에, 직접 확인해본 결과, 모든 케이스에 대하여 입력이 되어 있음을 확인할 수 있었고, 이로 인해 CASE WHEN을 이용하여 조건 분기를 통해 처리할 수 있었다.

SQL

SELECT *
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN

결과

C.DISCOUNT_RATE를 활용

3번 과정을 진행할 때,

WHEN DATEDIFF(B.END_DATE, B.START_DATE) + 1 BETWEEN 7 AND 30 
THEN ROUND(A.DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1) * (1 - C.DISCOUNT_RATE/100))

와 같이 SQL을 작성하였더니, 7일 이상/30일 이상/90일 이상 모두에 대한 계산을 진행하게 되는 것을 알게 되었다.
이를 해결하기 위해 서브 쿼리를 통해 처리해주었다.

WHEN DATEDIFF(B.END_DATE, B.START_DATE) + 1 BETWEEN 7 AND 30 
THEN ROUND(A.DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1) * (1 - (SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE = '7일 이상' AND CAR_TYPE = '트럭')/100))

이렇게 정답 판정을 받았는데, 이게 가독성이 좋은, 성능이 좋은 쿼리라는 생각은 들지 않는다.
다른 사람들이 작성한 정답 코드를 보며 개선할 부분을 찾을 필요가 있을 것 같다.