[MySQL] 프로그래머스 - 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (할인가격 구하기, WHERE)

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

프로그래머스

 

 

문제

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를 기준으로 내림차순 정렬해주세요.

---

 

구현 과정

• 자동차 종류가 '세단' 또는 'SUV'인 자동차 중 2022년 11월 1일부터 2022년 11월 30일 까지 대여가능한 자동차 찾기 
• 할인율을 계산한 대여금액 (FEE) 구하기 
• 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차 찾기 

 

1️⃣ 자동차 종류가 '세단' 또는 'SUV'인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여가능한 자동차 

1) 자동차 종류가 표시되어 있는 CAR_RENTAL_COMPANY_CAR 테이블과 대여 기록이 남아있는 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블을 JOIN 한다. CAR_ID를 기준으로 INNER JOIN.

 

2) 자동차 종류가 '세단'또는 'SUV'인 자동차를 찾기 위해 WHERE ~ IN 목록으로 CAR_ID가 ('세단', 'SUV') 에 포함되어 있는지 여부를 확인한다. 

 

3) 아래의 두 조건에 모두 포함되어 있는 자동차는 2022년 11월 1일 ~ 2022년 11월 30일에 대여할 수 없다. 

• 대여 시작일이 2022년 12월 1일 이전 
• 대여 종료일이 2022년 11월 1일 이후 

SELECT CAR_ID 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE END_DATE > '2022-11-01' AND START_DATE < '2022-12-01'

👉2022년 11월 1일 ~ 2022년 11월 30일에 대여할 수 없는 자동차 ID 목록

 

즉, 위 조건을 만족하는 자동차를 '제외'한 나머지 자동차들은 해당 기간 동안 대여가 가능하다. WHERE ~ NOT IN 목록으로 대여할 수 있는 자동차 목록을 조회한다. 

SELECT A.CAR_ID, A.CAR_TYPE
FROM CAR_RENTAL_COMPANY_CAR A 
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B 
ON A.CAR_ID = B.CAR_ID 
WHERE A.CAR_TYPE IN ('세단', 'SUV') AND 
A.CAR_ID NOT IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                 WHERE END_DATE > '2022-11-01' AND START_DATE < '2022-12-01')

 

2️⃣ 할인율을 계산한 대여금액 (FEE) 구하기 

30일간 대여하기 때문에 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류, 기간(30일)에 맞는 할인율을 얻어온다.

SELECT DISCOUNT_RATE 
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE CAR_TYPE = A.CAR_TYPE 
AND DURATION_TYPE ='30일 이상'

👉자동차 종류에 따른 할인율 조회 

• 할인된 대여금액(30일)  : (일일 대여요금 * (1 - (할인율)/100)) * 30 

 

위와 같이 계산하면 뒤에 소수점 .000이 표시되기 때문에 ROUND 함수를 사용하여 소수점을 모두 버린다. 또한 컬럼명을 FEE로 지정하기 위해 AS 연산자로 별칭을 지정한다.

SELECT A.CAR_ID, A.CAR_TYPE,
ROUND(A.DAILY_FEE * (1-(SELECT DISCOUNT_RATE 
                         FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                         WHERE CAR_TYPE = A.CAR_TYPE AND DURATION_TYPE ='30일 이상')/100) *30) 
                         AS FEE

 

3️⃣ 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차 찾기 

결과를 CAR_ID 별로 그룹화 하여 HAVING 절로 FEE 값이 50만원과 1999999 사이인 자동차만 조회한다. (200만원 미만)

 

---

✔️코드

SELECT A.CAR_ID, A.CAR_TYPE,
ROUND(A.DAILY_FEE * (1-(SELECT DISCOUNT_RATE 
                         FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                         WHERE CAR_TYPE = A.CAR_TYPE AND DURATION_TYPE ='30일 이상')/100) *30) AS FEE 

FROM CAR_RENTAL_COMPANY_CAR A 
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B 
ON A.CAR_ID = B.CAR_ID 
WHERE A.CAR_TYPE IN ('세단', 'SUV') AND 
A.CAR_ID NOT IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                 WHERE END_DATE > '2022-11-01' AND START_DATE < '2022-12-01')
GROUP BY A.CAR_ID 
HAVING FEE BETWEEN 500000 AND 1999999
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC;