[SQL] 프로그래머스 자동차 세트

평균 일일 대여 요금 구하기

프로그래머스

SELECT ROUND(AVG(DAILY_FEE),0) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE LIKE 'SUV';

 

자동차 대여 기록에서 장기/단기 대여 구분하기

SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE, DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE, 
IF(DATEDIFF(END_DATE, START_DATE) >= 29, '장기 대여', '단기 대여' )AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09%'
ORDER BY HISTORY_ID DESC;

 

특정 옵션이 포함된 자동차 리스트 구하기

SELECT *
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC;

 

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

SELECT car_type , count(*) as cars
from CAR_RENTAL_COMPANY_CAR
where options REGEXP '통풍시트|열선시트|가죽시트'
group by car_type
order by car_type

 

자동차 평균 대여 기간 구하기

SELECT CAR_ID, DATEAVG AS AVERAGE_DURATION
FROM 
    (SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE,START_DATE)+1),1) AS DATEAVG
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
     GROUP BY CAR_ID
     ) A
WHERE A.DATEAVG >= 7
ORDER BY A.DATEAVG DESC, A.CAR_ID DESC

 

대여 기록이 존재하는 자동차 리스트 구하기

SELECT DISTINCT C.CAR_ID
FROM CAR_RENTAL_COMPANY_CAR C LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID=H.CAR_ID
WHERE C.CAR_TYPE LIKE "세단" AND MONTH(H.START_DATE) = 10
ORDER BY C.CAR_ID DESC;

 

자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

SELECT CAR_ID, CASE WHEN max('2022-10-16' BETWEEN START_DATE AND END_DATE) THEN '대여중'
     ELSE '대여 가능' END AS AVAILAVILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

 

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

WITH HIS AS (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE DATE_FORMAT(START_DATE, '%Y-%m') >= '2022-08' AND DATE_FORMAT(START_DATE, '%Y-%m') <= '2022-10'
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5
)
SELECT MONTH(START_DATE) AS MONTH, C.CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY C RIGHT JOIN HIS H ON C.CAR_ID=H.CAR_ID
GROUP BY MONTH, CAR_ID
HAVING MONTH IN (8,9,10)
ORDER BY MONTH ASC, CAR_ID DESC;

 

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

WITH CAR AS (
    SELECT C.CAR_ID, CAR_TYPE, DAILY_FEE, MAX(START_DATE), MAX(END_DATE),
       IF(MAX(START_DATE) > '2022-11-30' OR MAX(END_DATE) <'2022-11-01', true, false) AS RANT
    FROM CAR_RENTAL_COMPANY_CAR C JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H 
        ON C.CAR_ID LIKE H.CAR_ID
    WHERE C.CAR_TYPE IN ('세단', 'SUV') 
    GROUP BY CAR_ID
    ORDER BY CAR_ID ASC, START_DATE ASC
    
)
SELECT CAR_ID, C.CAR_TYPE, ROUND(DAILY_FEE*RATE*0.01*30) AS FEE
FROM CAR C LEFT JOIN (SELECT CAR_TYPE, 100-DISCOUNT_RATE AS RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE LIKE '30일 이상' ) D 
ON C.CAR_TYPE = D.CAR_TYPE
WHERE C.RANT IS TRUE AND DAILY_FEE*RATE*0.01*30 BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;

 

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

WITH DISCOUNT AS (
    SELECT (CASE WHEN DURATION_TYPE LIKE '7일 이상' THEN 7
                      WHEN DURATION_TYPE LIKE '30일 이상' THEN 30
                      WHEN DURATION_TYPE LIKE '90일 이상' THEN 90 END
                     )AS DURATION, (100 - DISCOUNT_RATE) * 0.01 AS DISCOUNT_RATE
              FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
              WHERE CAR_TYPE LIKE '트럭'
),
TRUK AS (
    SELECT HISTORY_ID, DATEDIFF(END_DATE, START_DATE)+1 AS DAYS, 
                (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 0 END) AS DAY_TYPE, DAILY_FEE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H LEFT JOIN CAR_RENTAL_COMPANY_CAR C ON C.CAR_ID=H.CAR_ID
    WHERE CAR_TYPE LIKE '트럭'
)
SELECT HISTORY_ID, ROUND(DAILY_FEE * IFNULL(DISCOUNT_RATE, 1) * DAYS) AS FEE
# select *
FROM DISCOUNT D RIGHT JOIN TRUK T ON T.DAY_TYPE=D.DURATION
ORDER BY FEE DESC, HISTORY_ID DESC;