코드카타 Level.5 74, 75

74: 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

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

정답 코드:

SELECT
    cc.CAR_ID,
    cc.CAR_TYPE,
    FLOOR(cc.DAILY_FEE * 30 * (100 - dp.DISCOUNT_RATE) / 100) AS FEE
FROM CAR_RENTAL_COMPANY_CAR cc
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN dp
    ON dp.CAR_TYPE = cc.CAR_TYPE
    AND dp.DURATION_TYPE = '30일 이상'
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY rh
    ON rh.CAR_ID = cc.CAR_ID
    # 2022년 11월 1일부터 2022년 11월 30일까지 대여된 자동차
    AND rh.START_DATE < '2022-12-01'
    AND rh.END_DATE   >= '2022-11-01'
WHERE cc.CAR_TYPE in ('세단', "SUV") 
    AND rh.HISTORY_ID IS NULL       # 대여기록이 없는 자동차
    AND (cc.DAILY_FEE * 30 * (100 - dp.DISCOUNT_RATE) / 100) >= 500000
    AND (cc.DAILY_FEE * 30 * (100 - dp.DISCOUNT_RATE) / 100) < 2000000
ORDER BY FEE DESC, cc.CAR_TYPE ASC, cc.CAR_ID DESC;

문제 풀이때 했던 생각.

# 차랑정보 CAR_RENTAL_COMPANY_CAR cc # 자동차 대여 기록 정보 CAR_RENTAL_COMPANY_RENTAL_HISTORY  rh # 대여기간별 할인 정책 CAR_RENTAL_COMPANY_DISCOUNT_PLAN  dp # 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 # 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능 # 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차 # 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력 # 결과는 대여 금액을 기준으로 내림차순 정렬 # 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬 # 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬

 

---

 

75: 자동차 대여 기록 별 대여 금액 구하기

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

정답 코드:

SELECT
    rh.HISTORY_ID,
    FLOOR(
        cc.DAILY_FEE
        * (DATEDIFF(rh.END_DATE, rh.START_DATE) + 1)  # 날짜 차이
        * (100 - IFNULL(dp.DISCOUNT_RATE, 0)) / 100
    ) AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY rh
JOIN CAR_RENTAL_COMPANY_CAR cc
    ON cc.CAR_ID = rh.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN dp
    ON dp.CAR_TYPE = cc.CAR_TYPE
    AND dp.DURATION_TYPE =
        CASE
            WHEN (DATEDIFF(rh.END_DATE, rh.START_DATE) + 1) >= 90 THEN '90일 이상'
            WHEN (DATEDIFF(rh.END_DATE, rh.START_DATE) + 1) >= 30 THEN '30일 이상'
            WHEN (DATEDIFF(rh.END_DATE, rh.START_DATE) + 1) >= 7  THEN '7일 이상'
            ELSE NULL
        END
WHERE cc.CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC

 

문제 풀이때 했던 생각.

# 차랑정보 CAR_RENTAL_COMPANY_CAR cc # 자동차 대여 기록 정보 CAR_RENTAL_COMPANY_RENTAL_HISTORY  rh # 대여기간별 할인 정책 CAR_RENTAL_COMPANY_DISCOUNT_PLAN  dp # 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해 # 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력 # 대여 금액을 기준으로 내림차순 정렬, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬

 

---