[프로그래머스][SQL] 자동차 대여 관련 문제

자동차 대여 관련 문제 - 프로그래머스 lv 2, 3, 4

` CAR_RENTAL_COMPANY_CAR`

 

 

 

자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

 

제출한 답안

SELECT CAR_TYPE, COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE (OPTIONS LIKE '%통풍시트%') 
OR (OPTIONS LIKE '%열선시트%') OR (OPTIONS LIKE '%가죽시트%')
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE

 

 

 

대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

 

풀이 과정

SELECT CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE (START_DATE BETWEEN '2022-08-01' AND '2022-10-31')
AND (RECORDS >= 5)
GROUP BY CAR_ID
ORDER BY MONTH(START_DATE), CAR_ID DESC

 

>> Unknown column 'RECORDS' in 'where clause'

 

RECORDS는 집계함수(COUNT)를 사용하였으므로 HAVING 절에 사용되어야 한다

 

 

최종 제출 답안

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE (START_DATE BETWEEN '2022-08-01' AND '2022-10-31')
AND (CAR_ID IN (SELECT CAR_ID 
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
                GROUP BY CAR_ID
                HAVING COUNT(*) >= 5))
GROUP BY MONTH, CAR_ID
ORDER BY MONTH, CAR_ID DESC

 

 

 

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

-- 코드를 입력하세요
SELECT HIS.HISTORY_ID, CAR.DAILY_FEE * (DATEDIFF(HIS.END_DATE - HIS.START_DATE) + 1) *
                   (SELECT PLAN.DISCOUNT_RATE
                    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN PLAN
                    WHERE (PLAN.CAR_TYPE = CAR.CAR_TYPE)
                    AND (IF(DATEDIFF(HIS.END_DATE - HIS.START_DATE) + 1 BETWEEN 7 AND 29, DURATION_) )
                   ) AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY HIS
JOIN CAR_RENTAL_COMPANY_CAR CAR
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN PLAN
ON (HIS.CAR_ID = CAR.CAR_ID)
WHERE CAR.CAR_TYPE = '트럭'


IF(DURATION_TYPE = '7일이상')

CAR.DAILY_FEE * (DATEDIFF(HIS.END_DATE - HIS.START_DATE) + 1) * PLAN.DISCOUNT_RATE

-- 코드를 입력하세요
SELECT HIS.HISTORY_ID, CAR.DAILY_FEE * (DATEDIFF(HIS.END_DATE, HIS.START_DATE) + 1) *
                   (1 - PLAN.DISCOUNT_RATE / 100) AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY HIS
JOIN CAR_RENTAL_COMPANY_CAR CAR ON (HIS.CAR_ID = CAR.CAR_ID)
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN PLAN ON (CAR.CAR_TYPE = PLAN.CAR_TYPE)

WHERE (CAR.CAR_TYPE = '트럭') and
    (DATEDIFF(HIS.END_DATE, HIS.START_DATE) + 1 >= 
        CASE 
            WHEN PLAN.DURATION_TYPE = '7일 이상' THEN 7
            WHEN PLAN.DURATION_TYPE = '30일 이상' THEN 30
            WHEN PLAN.DURATION_TYPE = '90일 이상' THEN 90
        END
)
ORDER BY FEE DESC, HIS.HISTORY_ID DESC

 

-- 코드를 입력하세요
WITH TRUCK AS (SELECT HIS.HISTORY_ID, CAR.CAR_ID, 
               (CAR.DAILY_FEE) * (DATEDIFF(HIS.END_DATE, HIS.START_DATE) + 1) * (1 - DISCOUNT_RATE/100) AS FEE
               FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY HIS
               LEFT JOIN CAR_RENTAL_COMPANY_CAR CAR ON (CAR.CAR_ID = HIS.CAR_ID)
               JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN PLAN ON (PLAN.CAR_TYPE=CAR.CAR_TYPE)
               WHERE CAR.CAR_TYPE = '트럭'
              )


SELECT HIS.HISTORY_ID, TRUCK.FEE
FROM TRUCK
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS HIS
ON (TRUCK.HISTORY_ID = HIS.HISTORY_ID)
ORDER BY FEE DESC, HIS.HISTORY_ID DESC

 

 

 

다른 사람 풀이

WITH A AS (SELECT H.HISTORY_ID, C.DAILY_FEE
            , DATEDIFF(H.END_DATE, H.START_DATE)+1 AS 'RENTAL_DAYS' # FEE계산을 위한 렌탈 기간 구하기
            , CASE WHEN DATEDIFF(H.END_DATE, H.START_DATE)+1 >= 90 THEN '90일 이상'
            WHEN DATEDIFF(H.END_DATE, H.START_DATE)+1 >= 30 THEN '30일 이상'
            WHEN DATEDIFF(H.END_DATE, H.START_DATE)+1 >= 7 THEN '7일 이상'
            ELSE '7일 이하' END DURATION_TYPE # CAR_RENTAL_COMPANY_DISCOUNT_PLAN과 join하기 위함 
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
            JOIN CAR_RENTAL_COMPANY_CAR C ON C.CAR_ID = H.CAR_ID
        WHERE CAR_TYPE = '트럭')

SELECT A.HISTORY_ID
    , IF(B.DURATION_TYPE IS NULL, A.DAILY_FEE * A.RENTAL_DAYS, ROUND((A.DAILY_FEE * (100-B.DISCOUNT_RATE)*0.01) * A.RENTAL_DAYS,0)) AS 'FEE'
FROM A
LEFT JOIN (SELECT * FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE CAR_TYPE='트럭') B ON A.DURATION_TYPE = B.DURATION_TYPE
ORDER BY FEE DESC, HISTORY_ID DESC

 

참고한 블로그 링크