[프로그래머스 SQL] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

정답 쿼리

SELECT DISTINCT(C.CAR_ID), C.CAR_TYPE, ROUND(DAILY_FEE * 30 - (DAILY_FEE * 30 * (DISCOUNT_RATE/100))) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
ON C.CAR_ID = H.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON C.CAR_TYPE = P.CAR_TYPE
WHERE C.CAR_TYPE IN ('세단', 'SUV')
AND C.CAR_ID NOT IN (
                        SELECT CAR_ID
                        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                        WHERE START_DATE BETWEEN '2022-11-01' AND '2022-11-30'
                        OR END_DATE BETWEEN '2022-11-01' AND '2022-11-30'
                        OR (START_DATE <= '2022-11-01' AND END_DATE >= '2022-11-30'))
AND DURATION_TYPE = '30일 이상'
AND ROUND(DAILY_FEE * 30 - (DAILY_FEE * 30 * (DISCOUNT_RATE/100))) BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, C.CAR_TYPE, C.CAR_ID DESC;

해설

이 문제는 조건이 많아서 까다로웠지만, 총 5가지 조건으로 쪼개어서 풀이하면 쉽게 풀리는 문제였다. 

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

 

이 중 2번 문제에 대한 조건을 설정하는게 가장 까다로웠다. 그럼 가장 까다로웠던 2번 조건부터 하나씩 차근차근 풀이해보겠다.

 

2. 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고

SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE BETWEEN '2022-11-01' AND '2022-11-30'
OR END_DATE BETWEEN '2022-11-01' AND '2022-11-30'
OR (START_DATE <= '2022-11-01' AND END_DATE >= '2022-11-30'))

대여 기간에 관련된 컬럼은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에 있다. 여기에서 나는 아래 3가지로 다시 쪼개어서 조건을 설정해주었다. 

• start_date가 2022년 11월 1일부터 2022년 11월 30일 사이에 있으면 안된다.
• end_date가 2022년 11월 1일부터 2022년 11월 30일 사이에 있으면 안된다.
• start_date가 2022년 11월 1일보다 작고, end_date가 2022년 11월 30일보다 크면 안된다.

이 조건을 주석으로 적어놓고, 그대로 WHERE절을 통해서 조건문을 만들어 주었다. 이 때, 서브쿼리를 이용했는데, WHERE절에서 '안된다'에 해당하는 조건을 걸기 위해 NOT IN을 사용해야했다. 그래서 서브쿼리에는 반대로 '된다'에 해당하는 조건을 넣어주었다. SELECT문에는 식별키인 CAR_ID을 넣어주어서 이 조건에 해당되는 CAR_ID는 전부 제외시키도록 했다.

 

 

1. 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중

WHERE C.CAR_TYPE IN ('세단', 'SUV')
AND C.CAR_ID NOT IN (
                        SELECT CAR_ID
                        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                        WHERE START_DATE BETWEEN '2022-11-01' AND '2022-11-30'
                        OR END_DATE BETWEEN '2022-11-01' AND '2022-11-30'
                        OR (START_DATE <= '2022-11-01' AND END_DATE >= '2022-11-30'))

 

위 조건을 서브쿼리로 만들어주고, 1번 조건과 함께 WHERE절에 AND로 묶어주었다. 

 

 

3. 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서

WHERE C.CAR_TYPE IN ('세단', 'SUV')
AND C.CAR_ID NOT IN (
                        SELECT CAR_ID
                        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                        WHERE START_DATE BETWEEN '2022-11-01' AND '2022-11-30'
                        OR END_DATE BETWEEN '2022-11-01' AND '2022-11-30'
                        OR (START_DATE <= '2022-11-01' AND END_DATE >= '2022-11-30'))
AND DURATION_TYPE = '30일 이상'
AND ROUND(DAILY_FEE * 30 - (DAILY_FEE * 30 * (DISCOUNT_RATE/100))) BETWEEN 500000 AND 2000000

3번 조건 또한 AND로 WHERE절에 같이 묶어주었다.

 

 

4. 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요.

SELECT DISTINCT(C.CAR_ID), C.CAR_TYPE, ROUND(DAILY_FEE * 30 - (DAILY_FEE * 30 * (DISCOUNT_RATE/100))) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
ON C.CAR_ID = H.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON C.CAR_TYPE = P.CAR_TYPE
WHERE C.CAR_TYPE IN ('세단', 'SUV')
AND C.CAR_ID NOT IN (
                        SELECT CAR_ID
                        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                        WHERE START_DATE BETWEEN '2022-11-01' AND '2022-11-30'
                        OR END_DATE BETWEEN '2022-11-01' AND '2022-11-30'
                        OR (START_DATE <= '2022-11-01' AND END_DATE >= '2022-11-30'))
AND DURATION_TYPE = '30일 이상'
AND ROUND(DAILY_FEE * 30 - (DAILY_FEE * 30 * (DISCOUNT_RATE/100))) BETWEEN 500000 AND 2000000

join을 통해 3개의 테이블을 모두 묶어주고(결측치가 없어서 inner join을 하든, left join을 하든, right join을 하든 결과는 똑같다.) fee에 대한 리스트를 계산하여 select문에 넣어주었다.(대여금액 * 30일 - 할인 된 금액)

 

 

5. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

SELECT DISTINCT(C.CAR_ID), C.CAR_TYPE, ROUND(DAILY_FEE * 30 - (DAILY_FEE * 30 * (DISCOUNT_RATE/100))) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
ON C.CAR_ID = H.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON C.CAR_TYPE = P.CAR_TYPE
WHERE C.CAR_TYPE IN ('세단', 'SUV')
AND C.CAR_ID NOT IN (
                        SELECT CAR_ID
                        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                        WHERE START_DATE BETWEEN '2022-11-01' AND '2022-11-30'
                        OR END_DATE BETWEEN '2022-11-01' AND '2022-11-30'
                        OR (START_DATE <= '2022-11-01' AND END_DATE >= '2022-11-30'))
AND DURATION_TYPE = '30일 이상'
AND ROUND(DAILY_FEE * 30 - (DAILY_FEE * 30 * (DISCOUNT_RATE/100))) BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, C.CAR_TYPE, C.CAR_ID DESC;

마지막으로 정렬해주면 끝난다!