안녕하세요! 데코입니다!
오늘은 프로그래머스 코딩테스트 연습에 있는 "자동차 대여 기록 별 대여 금액 구하기" 문제를 포스팅하려고 합니다!
바로 포스팅 시작할게요! :)
(출처 : https://school.programmers.co.kr/learn/courses/30/lessons/151141)
1. 문제 : 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기(Lv. 4)
2. 문제 설명
다음은 어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은
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, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다.
[ 테이블 구조 - CAR_RENTAL_COMPANY_CAR ]
| Column Name | TYPE | NULLABLE |
| CAR_ID | INTEGER | FALSE |
| CAR_TYPE | VARCHAR(255) | FALSE |
| DAILY_FEE | INTEGER | FALSE |
| OPTIONS | VARCHAR(255) | FALSE |
자동차 종류는 '세단', 'SUV', '승합차', '트럭', '리무진'이 있습니다.
자동차 옵션 리스트는 콤마(',')로 구분된 키워드 리스트(예: ''열선시트,스마트키,주차감지센서'')로 되어있으며,
키워드 종류는 '주차감지센서', '스마트키', '네비게이션', '통풍시트', '열선시트', '후방카메라', '가죽시트' 가 있습니다.
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있며,
HISTORY_ID, CAR_ID, START_DATE, END_DATE는 각각
자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.
[ 테이블 구조 - CAR_RENTAL_COMPANY_RENTAL_HISTORY ]
| Column Name | TYPE | NULLABLE |
| HISTORY_ID | INTEGER | FALSE |
| CA_RID | INTEGER | FALSE |
| START_DATE | DATE | FALSE |
| END_DATE | DATE | FALSE |
CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블은
아래와 같은 구조로 되어있으며,
PLAN_ID, CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE는 각
요금 할인 정책 ID, 자동차 종류, 대여 기간 종류, 할인율(%)을 나타냅니다.
[ 테이블 구조 - CAR_RENTAL_COMPANY_RENTAL_HISTORY ]
| Column Name | TYPE | NULLABLE |
| PLAN_ID | INTEGER | FALSE |
| CAR_TYPE | VARCHAR(255) | FALSE |
| DURATION_TYPE | VARCHAR(255) | FALSE |
| DISCOUNT_RATE | INTEGER | FALSE |
할인율이 적용되는 대여 기간 종류로는
'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를 기준으로 내림차순 정렬해주세요.
[ 예시 ]
자동차 종류가 '트럭' 인 자동차의 대여 기록에 대해서 대여 기간을 구하면, 다음과 같습니다.
> 대여 기록 ID가 1인 경우, 7일
> 대여 기록 ID가 2인 경우, 2일
> 대여 기록 ID가 5인 경우, 30일
대여 기간 별로 일일 대여 요금에 알맞은 할인율을 곱하여 금액을 구하면 다음과 같습니다.
대여 기록 ID가 1인 경우, 일일 대여 금액 26,000원에서 5% 할인율을 적용하고 7일을 곱하면 총 대여 금액은 172,900원
대여 기록 ID가 2인 경우, 일일 대여 금액 26,000원에 2일을 곱하면 총 대여 금액은 52,000원
대여 기록 ID가 5인 경우, 일일 대여 금액 32,000원에서 7% 할인율을 적용하고 30일을 곱하면 총 대여 금액은 892,800원이 되므로,
대여 금액을 기준으로 내림차순 정렬 및 대여 기록 ID를 기준으로 내림차순 정렬하면 다음과 같아야 합니다.
[ 출력 결과 ]
| HISTORY_ID | FEE |
| 5 | 892800 |
| 1 | 172900 |
| 2 | 52000 |
[ 주의사항 ]
FEE의 경우 예시처럼 정수부분만 출력되어야 합니다.
3. 문제 풀이
SELECT D.HISTORY_ID
, ROUND(IF(D.DISCOUNT_RATE IS NULL, D.DAILY_FEE * D.DATE_DIFF, D.DAILY_FEE * D.DATE_DIFF * (100-D.DISCOUNT_RATE)*0.01),0) AS FEE
FROM (
SELECT A.*
, B.HISTORY_ID
, DATEDIFF(B.END_DATE,B.START_DATE) + 1 AS DATE_DIFF
, C.DISCOUNT_RATE
FROM CAR_RENTAL_COMPANY_CAR A
LEFT
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B
ON A.CAR_ID = B.CAR_ID
LEFT
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C
ON CASE WHEN DATEDIFF(B.END_DATE,B.START_DATE) BETWEEN 7 AND 29 THEN C.PLAN_ID = 10
WHEN DATEDIFF(B.END_DATE,B.START_DATE) BETWEEN 30 AND 89 THEN C.PLAN_ID = 11
WHEN DATEDIFF(B.END_DATE,B.START_DATE) >= 90 THEN C.PLAN_ID = 12 END
WHERE A.CAR_TYPE = '트럭'
) D
ORDER
BY FEE DESC
, HISTORY_ID DESC
이번 문제는 1안 한 가지로 작성했습니다!
# 1안 풀이 설명
# [ 문제 요구사항 ]
문제를 보면 이 문제를 풀기 위해서는 크게 다섯 가지 작업이 필요해요!
1. 자동차 종류가 '트럭'인 자동차 필터링 작업
2. 자동차 대여 일자 계산하기
3. 대여 일자에 맞게 할인률 반영하여 JOIN 하기
4. 일일 대여 금액, 대여 일자, 할인율을 반영한 대여 금액 계산하기
5. 대여 금액 내림차순, 대여 기록 ID 내림차순 정렬
# [ 1안 쿼리 작성 설명 ]
먼저 첫 번째 작업을 해결하기 위해서 CAR_RENTAL_COMPANY_CAR 테이블에
CAR_TYPE이 '트럭'인 자동차만 선택하는 쿼리를 작성했어요!
AND CAR.CAR_TYPE = '트럭'
두 번째 작업은 자동차 대여 일자 계산하는 것인데요!
START_DATE부터 END_DATE까지 대여한 일자를 구하기 위해
DATEDIFF(B.END_DATE,B.START_DATE) + 1을 사용해서 쿼리를 작성했어요!
1을 더해준 이유에 대해서는
2023-06-19 ~ 2023-06-19 와 같이 하루만 대여를 한 경우에는 0일이 나오는 것을 발견해서
이를 보정하고자 +1을 해주었습니다!
, DATEDIFF(B.END_DATE,B.START_DATE) + 1 AS DATE_DIFF
세 번째 작업은, 대여 일자에 맞는 할인율을 반영하여 JOIN하는 과정이에요!
대여 일자를 'DATE_DIFF'로 생성하였고
CASE 구문내에서 DATE_DIFF를 통해 대여 일수에 따라 PLAN_ID를 매칭하여
LEFT JOIN 하도록 쿼리를 작성했습니다!
SELECT A.*
, B.HISTORY_ID
, DATEDIFF(B.END_DATE,B.START_DATE) + 1 AS DATE_DIFF
, C.DISCOUNT_RATE
FROM CAR_RENTAL_COMPANY_CAR A
LEFT
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B
ON A.CAR_ID = B.CAR_ID
LEFT
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C
ON CASE WHEN DATEDIFF(B.END_DATE,B.START_DATE) BETWEEN 7 AND 29 THEN C.PLAN_ID = 10
WHEN DATEDIFF(B.END_DATE,B.START_DATE) BETWEEN 30 AND 89 THEN C.PLAN_ID = 11
WHEN DATEDIFF(B.END_DATE,B.START_DATE) >= 90 THEN C.PLAN_ID = 12
END
WHERE A.CAR_TYPE = '트럭'
네 번째 작업은, 일일 대여 금액, 대여 일자, 할인율을 반영한 대여 금액(FEE)를 계산하였어요!
LEFT JOIN으로 할인율을 적용해서 본 결과,
할인이 적용되지 않는 데이터에는 할인율이 NULL 값으로 들어가 있었어요!
그래서 IF와 IS NULL 함수를 통해
할인율이 NULL 값인 경우 : 일일 대여 금액 * 대여 일자 = 대여 금액(FEE)
할인율이 있는 경우 : 일일 대여 금액 * 대여 일자 * (100-할인율) * 100 = 대여 금액(FEE)
이렇게 계산되도록 쿼리를 작성했어요!
SELECT D.HISTORY_ID
, ROUND(IF(D.DISCOUNT_RATE IS NULL, D.DAILY_FEE * D.DATE_DIFF
, D.DAILY_FEE * D.DATE_DIFF * (100-D.DISCOUNT_RATE)*0.01),0) AS FEE
마지막으로, 문제에서 요구한 정렬 조건과 같이
ORDER BY를 이용해서
대여 금액을 나타내는 FEE 컬럼을 기준으로 내림차순,
대여 기록 ID를 타나내는 HISTORY_ID를 기준으로 내림차순 정렬했어요!!
ORDER
BY FEE DESC
, HISTORY_ID DESC
4. 문제 풀이 후기
이번 문제는 3개의 테이블을 활용하여 조인도 수행하고 서브쿼리도 활용한 문제예요!
저번 포스팅에서 풀이한 '특정 기간동안 대여 가능한 자동차들의 대여비용 구하기'문제와
유사한 유형이라고 생각이 들어요!
문제에서 요구한 내용에 맞게, 테이블에서 정보를 가져와서 하나하나 진행하면
누구나 충분히 풀 수 있을 것이라고 생각됩니다!
이렇게 이번 문제는 3개의 테이블을 활용하여 조인, 서브 쿼리, IF, DATEDIFF 함수 등
테이블을 결합하고, 요구사항에 맞게 필터링하고 값들을 다루는 문제예요!
설명이 어려운 부분 혹은 잘 이해가 안 가는 부분
그리고 더 궁금한 내용이 있다면
언제든지 댓글로 남겨주세요!
빠르게 궁금증을 해결해 드릴게요!
공감과 댓글은 큰 힘이 됩니다!
오늘도 블로그 방문해 주시고
포스팅 읽어주셔서 감사합니다!
'Skill Sets > SQL' 카테고리의 다른 글
| [SQL] 카테고리 별 도서 판매량 집계하기(프로그래머스/MySQL/Level 3) (33) | 2023.07.16 |
|---|---|
| [SQL] 저자 별 카테고리 별 매출액 집계하기(프로그래머스/MySQL/Level 4) (34) | 2023.07.10 |
| [SQL] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기(프로그래머스/MySQL/Level 4) (48) | 2023.06.08 |
| [SQL] 대여 기록이 존재하는 자동차 리스트 구하기(프로그래머스/MySQL/Level 3) (38) | 2023.04.25 |
| [SQL] 조건에 맞는 사용자 정보 조회하기(프로그래머스/MySQL/Level 3) (16) | 2023.04.21 |
