https://school.programmers.co.kr/learn/courses/30/lessons/151141
다음은 어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR 테이블과 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 자동차 종류 별 대여 기간 종류 별 할인 정책 정보를 담은 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블 입니다.
CAR_RENTAL_COMPANY_CAR 테이블은 아래와 같은 구조로 되어있으며, CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS 는 각각 자동차 ID, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다.
자동차 종류는 '세단', 'SUV', '승합차', '트럭', '리무진' 이 있습니다. 자동차 옵션 리스트는 콤마(',')로 구분된 키워드 리스트(예: ''열선시트,스마트키,주차감지센서'')로 되어있으며, 키워드 종류는 '주차감지센서', '스마트키', '네비게이션', '통풍시트', '열선시트', '후방카메라', '가죽시트' 가 있습니다.
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID, CAR_ID, START_DATE, END_DATE 는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.
CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블은 아래와 같은 구조로 되어있으며, PLAN_ID, CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE 는 각각 요금 할인 정책 ID, 자동차 종류, 대여 기간 종류, 할인율(%)을 나타냅니다.
할인율이 적용되는 대여 기간 종류로는 '7일 이상' (대여 기간이 7일 이상 30일 미만인 경우), '30일 이상' (대여 기간이 30일 이상 90일 미만인 경우), '90일 이상' (대여 기간이 90일 이상인 경우) 이 있습니다. 대여 기간이 7일 미만인 경우 할인정책이 없습니다.
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.
FEE의 경우 예시처럼 정수부분만 출력되어야 합니다.
-- 종류가 '트럭'인 자동차의 대여 기록에 대해서
-- 기록별 대여 금액(FEE)을 구하여 대여 기록 ID와 대여 금액 리스트 출력
-- '트럭'인 자동차의 대여 기록
SELECT RENT.CAR_ID
, RENT.CAR_TYPE
, RENT.DAILY_FEE
, HSTR.HISTORY_ID
, HSTR.START_DATE
, HSTR.END_DATE
FROM CAR_RENTAL_COMPANY_CAR RENT
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY HSTR
ON RENT.CAR_ID = HSTR.CAR_ID
WHERE RENT.CAR_TYPE = '트럭';
-- '트럭' 자동차의 대여 기록에서 대여 일 수를 구하기
SELECT RENT.CAR_ID
, RENT.CAR_TYPE
, RENT.DAILY_FEE
, HSTR.HISTORY_ID
, HSTR.START_DATE
, HSTR.END_DATE
, DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 AS "DAYS"
FROM CAR_RENTAL_COMPANY_CAR RENT
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY HSTR
ON RENT.CAR_ID = HSTR.CAR_ID
WHERE RENT.CAR_TYPE = '트럭';
-- 최종..
SELECT DTYPE.HISTORY_ID
, DTYPE.CAR_ID
, DTYPE.CAR_TYPE
, DTYPE.DAILY_FEE
, DTYPE.START_DATE
, DTYPE.END_DATE
, DTYPE.DAYS
, DTYPE.DURATION_TYPE
, DISC.DURATION_TYPE
, ROUND(DTYPE.DAILY_FEE * DTYPE.DAYS * IF(DISCOUNT_RATE IS NULL,0,DISCOUNT_RATE)/100) AS FEE
FROM (
SELECT RENT.CAR_ID
, RENT.CAR_TYPE
, RENT.DAILY_FEE
, HSTR.HISTORY_ID
, HSTR.START_DATE
, HSTR.END_DATE
, DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 AS "DAYS"
, CASE WHEN ( DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 >= 7
AND DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 < 30)
THEN '7일 이상'
WHEN ( DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 >= 30
AND DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 < 90)
THEN '30일 이상'
WHEN ( DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 >= 90)
THEN '90일 이상'
ELSE ''
END AS "DURATION_TYPE"
FROM CAR_RENTAL_COMPANY_CAR RENT
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY HSTR
ON RENT.CAR_ID = HSTR.CAR_ID
WHERE RENT.CAR_TYPE = '트럭'
)AS DTYPE
INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN DISC
ON (DTYPE.DURATION_TYPE = DISC.DURATION_TYPE
AND DTYPE.CAR_TYPE = DISC.CAR_TYPE)
ORDER BY FEE DESC
, HISTORY_ID DESC
;
-- with절 사용
WITH TMP_DTYPE AS
(
SELECT RENT.CAR_ID
, RENT.CAR_TYPE
, RENT.DAILY_FEE
, HSTR.HISTORY_ID
, HSTR.START_DATE
, HSTR.END_DATE
, DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 AS "DAYS"
, CASE WHEN ( DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 >= 7
AND DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 < 30)
THEN '7일 이상'
WHEN ( DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 >= 30
AND DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 < 90)
THEN '30일 이상'
WHEN ( DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 >= 90)
THEN '90일 이상'
ELSE ''
END AS "DURATION_TYPE"
FROM CAR_RENTAL_COMPANY_CAR RENT
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY HSTR
ON RENT.CAR_ID = HSTR.CAR_ID
)
SELECT TMP_DTYPE.HISTORY_ID
, TMP_DTYPE.CAR_ID
, TMP_DTYPE.CAR_TYPE
, TMP_DTYPE.DAILY_FEE
, TMP_DTYPE.START_DATE
, TMP_DTYPE.END_DATE
, TMP_DTYPE.DAYS
, TMP_DTYPE.DURATION_TYPE
, DISC.DURATION_TYPE
, ROUND(TMP_DTYPE.DAILY_FEE * TMP_DTYPE.DAYS * (100-DISC.DISCOUNT_RATE)/100) AS FEE
FROM TMP_DTYPE
INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN DISC
ON (TMP_DTYPE.DURATION_TYPE = DISC.DURATION_TYPE
AND TMP_DTYPE.CAR_TYPE = DISC.CAR_TYPE)
ORDER BY FEE DESC
, HISTORY_ID DESC;
-- WITH 사용 답변 제출
WITH TMP_DTYPE AS
(
SELECT RENT.CAR_ID
, RENT.CAR_TYPE
, RENT.DAILY_FEE
, HSTR.HISTORY_ID
, HSTR.START_DATE
, HSTR.END_DATE
, DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 AS "DAYS"
, CASE WHEN ( DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 >= 7
AND DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 < 30)
THEN '7일 이상'
WHEN ( DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 >= 30
AND DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 < 90)
THEN '30일 이상'
WHEN ( DATEDIFF(HSTR.END_DATE,HSTR.START_DATE)+1 >= 90)
THEN '90일 이상'
ELSE '할인없음'
END AS "DURATION_TYPE"
FROM CAR_RENTAL_COMPANY_CAR RENT
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY HSTR
ON RENT.CAR_ID = HSTR.CAR_ID
WHERE RENT.CAR_TYPE = '트럭'
)
SELECT TMP_DTYPE.HISTORY_ID
, ROUND(TMP_DTYPE.DAILY_FEE * TMP_DTYPE.DAYS * (100-IF(DISCOUNT_RATE IS NULL,0,DISCOUNT_RATE))/100) AS FEE
FROM TMP_DTYPE
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN DISC
ON (TMP_DTYPE.DURATION_TYPE = DISC.DURATION_TYPE
AND TMP_DTYPE.CAR_TYPE = DISC.CAR_TYPE)
ORDER BY FEE DESC
, HISTORY_ID DESC;
-- 정답
WITH base AS (
SELECT
HISTORY_ID
,CASE
WHEN DATEDIFF(end_date,start_date)+1 < 7 THEN '노할인'
WHEN DATEDIFF(end_date,start_date)+1 >= 7 AND DATEDIFF(end_date,start_date)+1 < 30 THEN '7일 이상'
WHEN DATEDIFF(end_date,start_date)+1 >= 30 AND DATEDIFF(end_date,start_date)+1 < 90 THEN '30일 이상'
WHEN DATEDIFF(end_date,start_date)+1 >= 90 THEN '90일 이상'
END discount_flg
,DAILY_FEE*(DATEDIFF(end_date,start_date)+1) AS before_discount_total_fee
,car_type
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS t1
INNER JOIN CAR_RENTAL_COMPANY_CAR AS t2
ON t1.car_id = t2.car_id AND car_type = '트럭'
)
SELECT HISTORY_ID
, ROUND(before_discount_total_fee*(100-IF(DISCOUNT_RATE IS NULL,0,DISCOUNT_RATE))*0.01) AS FEE
FROM base AS t1
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS t2
ON t1.car_type = t2.car_type AND t1.discount_flg =t2.duration_type
ORDER BY 2 DESC,1 DESC;