[SQL KIT] (2024)특정 기간동안 대여 가능한 자동차들의 대여비용 구하기(설명/코드/정답)

 

문제 링크 : https://school.programmers.co.kr/learn/courses/30/lessons/157339

 

정상 코드.

SELECT CAR_ID, C.CAR_TYPE, FLOOR(30*DAILY_FEE*(1-DISCOUNT_RATE/100)) AS FEE 
FROM CAR_RENTAL_COMPANY_CAR C JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P USING(CAR_TYPE) 
WHERE C.CAR_TYPE IN ('세단', 'SUV') 
      AND CAR_ID NOT IN (SELECT CAR_ID
                         FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
                         WHERE END_DATE >= '2022-11-1' AND START_DATE <= '2022-11-30')
      AND DURATION_TYPE = '30일 이상'
      AND 30*DAILY_FEE*(1-DISCOUNT_RATE/100) >= 500000 
      AND 30*DAILY_FEE*(1-DISCOUNT_RATE/100) < 2000000 
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC

or

SELECT CAR_ID, C.CAR_TYPE, FLOOR(30*DAILY_FEE*(1-DISCOUNT_RATE/100)) AS FEE 
FROM CAR_RENTAL_COMPANY_CAR C JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P USING(CAR_TYPE) 
WHERE C.CAR_TYPE IN ('세단', 'SUV') 
      AND CAR_ID NOT IN (SELECT CAR_ID
                         FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
                         WHERE END_DATE >= '2022-11-1' AND START_DATE <= '2022-11-30')
      AND DURATION_TYPE = '30일 이상'
HAVING FEE >= 500000 AND FEE < 2000000  
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC

* GROUP BY 이하 절에서 SELECT에서 적용할 alias(별칭) 사용 가능

 

 

내부 동작 순서 

 

1. FROM 절

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블을 CAR_TYPE을 기준으로 조인합니다.

 

 

2. WHERE 절

WHERE C.CAR_TYPE IN ('세단', 'SUV'): 

CAR_TYPE이 '세단' 또는 'SUV'인 행만 선택합니다.

 

CAR_ID NOT IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE END_DATE >= '2022-11-1' AND START_DATE <= '2022-11-30'): 

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 11월 1일부터 2022년 11월 30일까지 대여된 CAR_ID를 제외한 행을 선택합니다.

 

DURATION_TYPE = '30일 이상': 

대여 기간이 30일 이상인 행만 선택합니다

 

30*DAILY_FEE*(1-DISCOUNT_RATE/100) >= 500000 AND 30*DAILY_FEE*(1-DISCOUNT_RATE/100) < 2000000: 

총대여비용이 500,000 이상이고 2,000,000 미만인 행만 선택합니다.

 

 

3. SELECT 절

CAR_ID, CAR_TYPE, 할인을 적용한 총대여비용을 계산하고 FEE라는 별칭으로 선택합니다. 

 

 

4. ORDER BY 절

결과를 FEE를 기준으로 내림차순으로 정렬하고, 그다음에는 CAR_TYPE을 오름차순으로 정렬하며, 마지막으로 CAR_ID를 내림차순으로 정렬합니다.

 

 

CAR_ID	CAR_TYPE	FEE
3	세단	1518000
23	세단	1380000

 

 

 

 

쿼리 작성 중 발생할 수 있는 실수. 

• 자동차 종류가 '세단' 또는 'SUV' 인 자동차 
  •  : IN 사용 대신 '=' 사용
• 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능
  • : 게시글 하단에 자세히 설명
• 대여 금액이 50만원 이상 200만원 미만인 자동차
  • : BETWEEN a AND b 범위  오인,  200만 원 이하로 필터링

 

 

 

 

---

 

초기 코드

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

 

 

CAR_ID	CAR_TYPE	FEE
3	세단	1518000
23	세단	1380000
27	SUV	655500
18	SUV	627000

 

=>오류

 

오류 원인.

잘못된 날짜 범위 지정으로 인한 필터링 오류  (CAR_ID 27과 18의 행이 필터링 안됨)

 

사용자가 원하는 기간인 2022년 11월 1일 ~ 2022년 11월 30일에 대여 중이 아닌 차가 조건.

 

기간 내 대여 중일 차를 먼저 식별.

2022년 11월 1일 ~ 2022년 11월 30일 대여 중인 경우(아래 4가지 경우)

END_DATE BETWEEN '2022-11-01' AND '2022-11-30' 
OR
START_DATE BETWEEN '2022-11-01' AND '2022-11-30'

 

초기 코드 오류 원인은  녹색 선인  대여 기간이 '2022-11-01' 이전에 시작되어 '2022-11-30' 이후에 종료되는 경우가 포함되지 않음.

 

 

샘플 데이터 특징 확인.

	최소	최대
START_DATE	2022-08-01	2022-10-31	* 제대로된 필터링이 아니어도 정답 처리될 가능성 있음 주의
END_DATE	2022-08-02	2023-01-26

 

				처리	코드 정확도
경우 1	END_DATE >= '2022-11-1'	AND	START_DATE <= '2022-11-30	정답..	O
경우 2	END_DATE >= '2022-11-1'	OR	START_DATE <= '2022-11-30	틀렸..
경우 3			START_DATE <= '2022-11-30	틀렸..
경우 4	END_DATE >= '2022-11-1'			정답..	X 예시.start 11/3 end 11/10 의 경우

 

 

 

==> 오류 시 전체 값을 출력해 보고 필터링이 잘 됐는지 검증 습관

==> 논리테이블 머리속에 그리면서 쿼리 짜기