[프로그래머스 SQL 코딩테스트 연습] String, Date - 자동차 대여 기록 별 대여 금액 구하기

🤔 문제

다음은 어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 `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를 기준으로 내림차순 정렬해주세요.

 

 

 

 

예시

예를 들어 `CAR_RENTAL_COMPANY_CAR` 테이블과

 

`CAR_RENTAL_COMPANY_RENTAL_HISTORY` 테이블과

`CAR_RENTAL_COMPANY_DISCOUNT_PLAN` 테이블이 다음과 같다면

 

자동차 종류가 '트럭' 인 자동차의 대여 기록에 대해서 대여 기간을 구하면,

• 대여 기록 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를 기준으로 내림차순 정렬하면 다음과 같아야 합니다.

주의사항

`FEE`의 경우 예시처럼 정수부분만 출력되어야 합니다.

 

 

 

 

 

 

 

 

 

 

---

💻 나의 풀이 (틀림)

WITH TRUCK_FEE AS(
    SELECT c.DAILY_FEE,
            c.CAR_TYPE,
			h.HISTORY_ID,
			DATEDIFF(END_DATE, START_DATE) + 1 AS PERIOD,
		    CASE
			    WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 7 THEN '7일 이상'
			    WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '30일 이상'
			    WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 90 THEN '90일 이상'
		    ELSE 'None'
		    END AS DURATION_TYPE
	FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
	INNER JOIN CAR_RENTAL_COMPANY_CAR c ON h.CAR_ID = c.CAR_ID
	WHERE c.CAR_TYPE = '트럭'
)
SELECT t.HISTORY_ID,
        ROUND(t.PERIOD * t.DAILY_FEE * (100-COALESCE(d.DISCOUNT_RATE, 0))/100) AS FEE
FROM TRUCK_FEE t
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d
ON t.CAR_TYPE=d.CAR_TYPE
AND t.DURATION_TYPE = d.DURATION_TYPE
ORDER BY 2 DESC, 1 DESC;

 

 

 

 

 

결과

 

 

 

 

 

틀린 이유

 

 

 

 

 

 

---

💡 다른 풀이

 

 

 

 

 

 

 

 

 

 

 

 

 

 

---

🔗 References

https://hellobrocolli.tistory.com/27

[MYSQL] 자동차 대여 기록별 대여 금액 구하기

 

 

 

 

 

 

 

 

 

---

🧘🏻‍♀️ 느낀 점

특히 SQL 코딩테스트 문제를 풀때는 미리 구조를 짜는 습관을 안들여서, 무작정 `SELECT ~ FROM ~`으로 시작했는데
고난이도 문제를 풀면서 그러면 안되겠다는 생각이 들기 시작했다.
그리고 생각보다 `WITH ~ AS ` 또는 서브쿼리를 작성해야 풀리는 문제들이 많은 것 같다.
근데 나는 아직도 `WITH ~ AS ` 구문이 문법 자체는 이해가 가는데 머리로 그려지지 않아서 너무 힘들다.
어떻게 큰 쿼리를 만들고 또 그걸 조인해서 원래 테이블과 합쳐서 연산할 수 있는건지 모르겠다... ㅠㅠ
연습만이 살 길이겠지 .. 같이 코테 스터디 하는 분들은 `WITH ~ AS` 구문도 척척 잘 쓰시는 것 같아서 부럽기도한 한편, 내가 너무 부족한 것 같아서 우울해진다.