📑 1. 문제설명




✏️ 2. 문제 요약
문제에서 주어진 조건
CAR_RENTAL_COMPANY_CAR | CAR_RENTAL_COMPANY_RENTAL_HISTORY | CAR_RENTAL_COMPANY _DISCOUNT_PLAN |
대여 중인 자동차들의 정보 | 자동차 대여 기록 정보 | 자동차 종류 별 대여 기간 종류 별 할인 정책 정보 |
CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS |
HISTORY_ID, CAR_ID, START_DATE, END_DATE |
PLAN_ID, CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE |
- 자동차 ID, - 자동차 종류, - 일일 대여 요금(원), - 자동차 옵션 리스트, |
- 자동차 대여 기록 ID, - 자동차 ID - 대여 시작일, - 대여 종료일 |
- 요금 할인 정책 ID, - 자동차 종류, - 대여 기간 종류, - 할인율(%) |
문제 요약
1) 자동차 종류가 '트럭'인 자동차
2) 대여 기록에 대해서, 대여 기록 별로 대여 기록 ID와 대여 금액 리스트를 출력하기
3) 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬
2-1. 대여 기록별 대여 금액을 계산하기 위해 필요한 컬럼
- 대여 기간 (period): 대여한 일수
- 일일 요금 (daily_fee): 하루에 청구되는 기본 금액
- 할인율 (discount_rate): 적용 가능한 할인율 (퍼센트 값)
자동차 종류별, 기간별로 할인율이 다른데 종류는 이미 '트럭'으로 한정되어 있기 때문에 서브쿼리에서 기간별로 JOIN을 해야 한다.
대여기간 구하기
car_rental_company_rental_history 테이블에서 DATEDIFF() + 1을 통해 대여 기간을 구해야 한다.
+ 1을 하는 이유는 빌린 당일에 반납해도 1일 대여로 계산하기 때문이다. 대여 기간을 먼저 구하고 해당 기간을 CASE문으로 (7일 이상, 30일 이상, 90일 이상)으로 구분하는 작업을 통해 대여기간 종류 컬럼인 DURATION_TYPE을 결정할 수 있다.
-> 여기서 구한 값을 WITH VALUE 라는 서브쿼리로 만들고 실제 데이터 검색은 이 서브쿼리 안에서 수행하기
따라서 서브쿼리와 실제 쿼리로 두 부분으로 구분해서 SQL문을 작성해야 함.
2-2. 서브쿼리(WITH value 구문) 에서 수행하는 작업
car_rental_company_rental_history 테이블의 `end_date`, `start_date` 컬럼을 가져와 DATEDIFF 함수 + 1 해서 대여 기간(period)을 계산한다.
이 값으로 CASE문을 분기해서 DURATION_TYPE 결정하기 (별칭 history)
위에서 필터링한 history와 car_rental_company_car 테이블을 car.car_id = history.car_id로 JOIN 한다.
자동차 종류가 '트럭'인 자동차만 필터링한다.
WITH VALUE AS (
SELECT CAR.DAILY_FEE, CAR.CAR_TYPE, HISTORY.HISTORY_ID,
DATEDIFF(END_DATE, START_DATE) + 1 AS PERIOD,
CASE
WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 90 THEN '90일 이상'
WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '30일 이상'
WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 7 THEN '7일 이상'
ELSE ''
END AS DURATION_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS HISTORY
INNER JOIN CAR_RENTAL_COMPANY_CAR AS CAR ON CAR.CAR_ID = HISTORY.CAR_ID
WHERE CAR.CAR_TYPE = '트럭')
2-3. 실제 SELECT 절에서 수행하는 작업
위에서 구한 WITH VALUE 의 결과를 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블과CAR_TYPE, DURATION_TYPE 컬럼으로 조인해서 최종 대여금액을 계산하고 정렬하기
대여 금액 계산하는 법
일일 요금 × 대여 기간 × (100% - 할인율)
value.daily_fee * value.period * (100 - IFNULL(plan.discount_rate,0)) / 100
할인율을 백분율로 계산하기 위해 (100 - discount_rate) / 100을 사용한다.
예) 할인율 10%라면, 금액에 90 / 100을 곱해 90%만 남기기
VALUE.DAILY_FEE * VALUE.PERIOD
DAILY_FEE는 하루 대여 비용이고, PERIOD는 대여 기간(일 수)이므로 이 두 값을 곱하면 기본 대여금액(할인 전 금액)을 계산할 수 있다.
IFNULL(PLAN.DISCOUNT_RATE, 0)
IFNULL 함수로 NULL 처리 해 주는 이유는 만약에 PLAN.DISCOUNT_RATE 값으로 NULL이 들어오는 경우에 NULL 처리를 하지 않으면, 연산에서 오류는 발생하지 않지만 결과가 항상 NULL이 되기 때문이다.
정렬
대여 금액(FEE) 기준 내림차순 정렬, 금액이 동일하면 history_id 기준 내림차순 정렬
ORDER BY 2 DESC, 1 DESC
전체 쿼리
SELECT VALUE.HISTORY_ID,
ROUND(VALUE.DAILY_FEE * VALUE.PERIOD *
(100 - IFNULL(PLAN.DISCOUNT_RATE,0)) / 100) AS FEE
FROM VALUE
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS PLAN
ON PLAN.DURATION_TYPE = VALUE.DURATION_TYPE
AND PLAN.CAR_TYPE = VALUE.CAR_TYPE
ORDER BY 2 DESC, 1 DESC
⭐ 3. 정답코드
WITH VALUE AS (
SELECT CAR.DAILY_FEE, CAR.CAR_TYPE, HISTORY.HISTORY_ID,
DATEDIFF(END_DATE, START_DATE) + 1 AS PERIOD,
CASE
WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 90 THEN '90일 이상'
WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '30일 이상'
WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 7 THEN '7일 이상'
ELSE ''
END AS DURATION_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS HISTORY
INNER JOIN CAR_RENTAL_COMPANY_CAR AS CAR ON CAR.CAR_ID = HISTORY.CAR_ID
WHERE CAR.CAR_TYPE = '트럭')
SELECT VALUE.HISTORY_ID,
ROUND(VALUE.DAILY_FEE * VALUE.PERIOD *
(100 - IFNULL(PLAN.DISCOUNT_RATE,0)) / 100) AS FEE
FROM VALUE
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS PLAN
ON PLAN.DURATION_TYPE = VALUE.DURATION_TYPE
AND PLAN.CAR_TYPE = VALUE.CAR_TYPE
ORDER BY 2 DESC, 1 DESC
📌 TMI
lv 4 문제라서 어렵긴 어렵네
'코딩테스트 > SQL테스트' 카테고리의 다른 글
[프로그래머스] (MySQL) 상품 별 오프라인 매출 구하기 (2) | 2025.05.18 |
---|---|
[프로그래머스] (MySQL) 취소되지 않은 진료 예약 조회하기 (9) | 2025.01.22 |
[프로그래머스] (MySQL) 오랜 기간 보호한 동물(2) (12) | 2025.01.22 |
[프로그래머스] (MySQL) 연도 별 평균 미세먼지 농도 조회하기 (20) | 2025.01.21 |
[프로그래머스] (MySQL) 카테고리 별 상품 개수 구하기 (6) | 2025.01.21 |
📑 1. 문제설명




✏️ 2. 문제 요약
문제에서 주어진 조건
CAR_RENTAL_COMPANY_CAR | CAR_RENTAL_COMPANY_RENTAL_HISTORY | CAR_RENTAL_COMPANY _DISCOUNT_PLAN |
대여 중인 자동차들의 정보 | 자동차 대여 기록 정보 | 자동차 종류 별 대여 기간 종류 별 할인 정책 정보 |
CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS |
HISTORY_ID, CAR_ID, START_DATE, END_DATE |
PLAN_ID, CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE |
- 자동차 ID, - 자동차 종류, - 일일 대여 요금(원), - 자동차 옵션 리스트, |
- 자동차 대여 기록 ID, - 자동차 ID - 대여 시작일, - 대여 종료일 |
- 요금 할인 정책 ID, - 자동차 종류, - 대여 기간 종류, - 할인율(%) |
문제 요약
1) 자동차 종류가 '트럭'인 자동차
2) 대여 기록에 대해서, 대여 기록 별로 대여 기록 ID와 대여 금액 리스트를 출력하기
3) 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬
2-1. 대여 기록별 대여 금액을 계산하기 위해 필요한 컬럼
- 대여 기간 (period): 대여한 일수
- 일일 요금 (daily_fee): 하루에 청구되는 기본 금액
- 할인율 (discount_rate): 적용 가능한 할인율 (퍼센트 값)
자동차 종류별, 기간별로 할인율이 다른데 종류는 이미 '트럭'으로 한정되어 있기 때문에 서브쿼리에서 기간별로 JOIN을 해야 한다.
대여기간 구하기
car_rental_company_rental_history 테이블에서 DATEDIFF() + 1을 통해 대여 기간을 구해야 한다.
+ 1을 하는 이유는 빌린 당일에 반납해도 1일 대여로 계산하기 때문이다. 대여 기간을 먼저 구하고 해당 기간을 CASE문으로 (7일 이상, 30일 이상, 90일 이상)으로 구분하는 작업을 통해 대여기간 종류 컬럼인 DURATION_TYPE을 결정할 수 있다.
-> 여기서 구한 값을 WITH VALUE 라는 서브쿼리로 만들고 실제 데이터 검색은 이 서브쿼리 안에서 수행하기
따라서 서브쿼리와 실제 쿼리로 두 부분으로 구분해서 SQL문을 작성해야 함.
2-2. 서브쿼리(WITH value 구문) 에서 수행하는 작업
car_rental_company_rental_history 테이블의 end_date
, start_date
컬럼을 가져와 DATEDIFF 함수 + 1 해서 대여 기간(period)을 계산한다.
이 값으로 CASE문을 분기해서 DURATION_TYPE 결정하기 (별칭 history)
위에서 필터링한 history와 car_rental_company_car 테이블을 car.car_id = history.car_id로 JOIN 한다.
자동차 종류가 '트럭'인 자동차만 필터링한다.
WITH VALUE AS ( SELECT CAR.DAILY_FEE, CAR.CAR_TYPE, HISTORY.HISTORY_ID, DATEDIFF(END_DATE, START_DATE) + 1 AS PERIOD, CASE WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 90 THEN '90일 이상' WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '30일 이상' WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 7 THEN '7일 이상' ELSE '' END AS DURATION_TYPE FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS HISTORY INNER JOIN CAR_RENTAL_COMPANY_CAR AS CAR ON CAR.CAR_ID = HISTORY.CAR_ID WHERE CAR.CAR_TYPE = '트럭')
2-3. 실제 SELECT 절에서 수행하는 작업
위에서 구한 WITH VALUE 의 결과를 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블과CAR_TYPE, DURATION_TYPE 컬럼으로 조인해서 최종 대여금액을 계산하고 정렬하기
대여 금액 계산하는 법
일일 요금 × 대여 기간 × (100% - 할인율)
value.daily_fee * value.period * (100 - IFNULL(plan.discount_rate,0)) / 100
할인율을 백분율로 계산하기 위해 (100 - discount_rate) / 100을 사용한다.
예) 할인율 10%라면, 금액에 90 / 100을 곱해 90%만 남기기
VALUE.DAILY_FEE * VALUE.PERIOD
DAILY_FEE는 하루 대여 비용이고, PERIOD는 대여 기간(일 수)이므로 이 두 값을 곱하면 기본 대여금액(할인 전 금액)을 계산할 수 있다.
IFNULL(PLAN.DISCOUNT_RATE, 0)
IFNULL 함수로 NULL 처리 해 주는 이유는 만약에 PLAN.DISCOUNT_RATE 값으로 NULL이 들어오는 경우에 NULL 처리를 하지 않으면, 연산에서 오류는 발생하지 않지만 결과가 항상 NULL이 되기 때문이다.
정렬
대여 금액(FEE) 기준 내림차순 정렬, 금액이 동일하면 history_id 기준 내림차순 정렬
ORDER BY 2 DESC, 1 DESC
전체 쿼리
SELECT VALUE.HISTORY_ID, ROUND(VALUE.DAILY_FEE * VALUE.PERIOD * (100 - IFNULL(PLAN.DISCOUNT_RATE,0)) / 100) AS FEE FROM VALUE LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS PLAN ON PLAN.DURATION_TYPE = VALUE.DURATION_TYPE AND PLAN.CAR_TYPE = VALUE.CAR_TYPE ORDER BY 2 DESC, 1 DESC
⭐ 3. 정답코드
WITH VALUE AS ( SELECT CAR.DAILY_FEE, CAR.CAR_TYPE, HISTORY.HISTORY_ID, DATEDIFF(END_DATE, START_DATE) + 1 AS PERIOD, CASE WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 90 THEN '90일 이상' WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '30일 이상' WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 7 THEN '7일 이상' ELSE '' END AS DURATION_TYPE FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS HISTORY INNER JOIN CAR_RENTAL_COMPANY_CAR AS CAR ON CAR.CAR_ID = HISTORY.CAR_ID WHERE CAR.CAR_TYPE = '트럭') SELECT VALUE.HISTORY_ID, ROUND(VALUE.DAILY_FEE * VALUE.PERIOD * (100 - IFNULL(PLAN.DISCOUNT_RATE,0)) / 100) AS FEE FROM VALUE LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS PLAN ON PLAN.DURATION_TYPE = VALUE.DURATION_TYPE AND PLAN.CAR_TYPE = VALUE.CAR_TYPE ORDER BY 2 DESC, 1 DESC
📌 TMI
lv 4 문제라서 어렵긴 어렵네
'코딩테스트 > SQL테스트' 카테고리의 다른 글
[프로그래머스] (MySQL) 상품 별 오프라인 매출 구하기 (2) | 2025.05.18 |
---|---|
[프로그래머스] (MySQL) 취소되지 않은 진료 예약 조회하기 (9) | 2025.01.22 |
[프로그래머스] (MySQL) 오랜 기간 보호한 동물(2) (12) | 2025.01.22 |
[프로그래머스] (MySQL) 연도 별 평균 미세먼지 농도 조회하기 (20) | 2025.01.21 |
[프로그래머스] (MySQL) 카테고리 별 상품 개수 구하기 (6) | 2025.01.21 |