특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

난이도 구분 : Level 4

문제

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

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

문제 풀이

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

SQL

SELECT A.CAR_ID
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. 자동차 종류 칼럼에서 '세단', 'SUV' 중 하나인 경우

SQL

SELECT DISTINCT A.CAR_ID
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 IN ('세단', 'SUV')

결과

3. 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능한 경우

SQL

SELECT DISTINCT A.CAR_ID
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 IN ('세단', 'SUV') 
AND (A.CAR_ID NOT IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-11' BETWEEN DATE_FORMAT(START_DATE, '%Y-%m') AND DATE_FORMAT(END_DATE, '%Y-%m')));

결과

4. 대여 금액이 50만원 이상 200만원 미만인 경우

SQL

SELECT DISTINCT A.CAR_ID, A.CAR_TYPE, ROUND(A.DAILY_FEE * 30 * (100 - C.DISCOUNT_RATE) / 100, 0) 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 IN ('세단', 'SUV') 
AND (A.CAR_ID NOT IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-11' BETWEEN DATE_FORMAT(START_DATE, '%Y-%m') AND DATE_FORMAT(END_DATE, '%Y-%m')))
AND (C.DURATION_TYPE = '30일 이상' AND (A.DAILY_FEE * 30 * (100 - C.DISCOUNT_RATE) / 100) BETWEEN 500000 AND 2000000);

결과

5. 대여 금액 내림차순 -> 자동차 종류 오름차순 -> 자동차 ID 내림차순

SQL

SELECT DISTINCT A.CAR_ID, A.CAR_TYPE, ROUND(A.DAILY_FEE * 30 * (100 - C.DISCOUNT_RATE) / 100, 0) 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 IN ('세단', 'SUV') 
AND (A.CAR_ID NOT IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-11' BETWEEN DATE_FORMAT(START_DATE, '%Y-%m') AND DATE_FORMAT(END_DATE, '%Y-%m')))
AND (C.DURATION_TYPE = '30일 이상' AND (A.DAILY_FEE * 30 * (100 - C.DISCOUNT_RATE) / 100) BETWEEN 500000 AND 2000000)
ORDER BY FEE DESC, A.CAR_TYPE ASC, A.CAR_ID DESC;

결과

정답 SQL

SELECT DISTINCT A.CAR_ID, A.CAR_TYPE, ROUND(A.DAILY_FEE * 30 * (100 - C.DISCOUNT_RATE) / 100, 0) 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 IN ('세단', 'SUV') 
AND (A.CAR_ID NOT IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-11' BETWEEN DATE_FORMAT(START_DATE, '%Y-%m') AND DATE_FORMAT(END_DATE, '%Y-%m')))
AND (C.DURATION_TYPE = '30일 이상' AND (A.DAILY_FEE * 30 * (100 - C.DISCOUNT_RATE) / 100) BETWEEN 500000 AND 2000000)
ORDER BY FEE DESC, A.CAR_TYPE ASC, A.CAR_ID DESC;

풀이 과정에서 기억할 점

2022년 11월에 대여 가능한 자동차를 찾는 부분

2022년 11월 1일부터 2022년 11월 30일까지 대여 가능한 경우에 대해 처음에는 START_DATE가 11월 30일 이후인 경우와 END_DATE가 11월 1일 이전인 경우에 대한 OR조건으로 처리했다.
SQL

SELECT DISTINCT A.CAR_ID, B.START_DATE, B.END_DATE
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 IN ('세단', 'SUV') AND (DATE_FORMAT(B.START_DATE, '%Y-%m-%d') > '2022-11-30' OR DATE_FORMAT(B.END_DATE, '%Y-%m-%d') < '2022-11-01')

결과

위와 같은 결과를 가져왔는데, 결국 한 자동차에 대해 여러번의 대여 기록이 있으므로 이를 구별해 내기 위하여서는 다른 방법으로 진행해야 하므로 BETWEEN A AND B를 사용할 때 START_DATE와 END_DATE 사이에 11월이 껴있지 않도록 하여서 구현하였다.

DISTINCT 처리 주의

SELECT DISTINCT A.CAR_ID, B.START_DATE, B.END_DATE로 조회 칼럼을 잡아서 조회하였더니 계속 동일한 CAR_ID가 출력되고 있었다. 왜 안 되는지 고민해보니 DISTINCT는 SELECT에 선택된 모든 칼럼에 대하여 중복 제거를 고려하기에 B.START_DATE와 B.END_DATE 중 하나라도 다르다면 중복이 아니라고 판단해서 출력하게 된다. 이런 부분 때문에 다음 단계로 넘어가는 과정에서 시간이 걸렸는데, DISTINCT 사용에 있어서 조심해야할 것 같다.