[프로그래머스 스쿨 - MYSQL] 자동차 대여 문제 Lv.1 ~Lv.4

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

문제

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성해주세요. 결과는 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

해설

추출해야할 것 history_id, car_id, start_date, end_date, rent_type

조건 start_date LIKE "%2022-09%"

정렬 history_id DESC

 

1. 2022년 09월에 대여 시작한 행을 불러온다

2. CASE WHEN ~ 절과 DATEDIFF를 통해서 "장기 대여"와 "단기 대여"를 출력한다

3. 조건에 맞게 정렬한다

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,
(CASE
WHEN DATEDIFF(end_date,start_date) + 1 >= 30 THEN "장기 대여" -- end_date까지 포함
ELSE  "단기 대여"
END) AS RENT_TYPE
FROM car_rental_company_rental_history 
WHERE DATE_FORMAT(start_date,"%Y-%m") LIKE "%2022-09%"
ORDER BY history_id DESC

 

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

문제

CAR_RENTAL_COMPANY_CAR 테이블에서 '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성해주세요. 이때 자동차 수에 대한 컬럼명은 CARS로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬해주세요.

해설

추출해야할 것 car_type, cars

조건 options에 '통풍시트', '열선시트', '가죽시트'  중 적어도 1개 포함

정렬 car_type ASC

 

1. options는 제 1정규화에 위배되었지만, LIKE 함수로 포함되었는지 여부를 파악해 필터링한다

2. car_type 별로 GROUP BY를 해준 후 COUNT로 집계해준다 

3. 조건에 맞게 정렬한다

SELECT car_type, COUNT(car_type) AS CARS
FROM car_rental_company_car
WHERE OPTIONS LIKE "%가죽시트%" OR OPTIONS LIKE "%열선시트%" OR OPTIONS LIKE "%통풍시트%"
GROUP BY car_type
ORDER BY car_type

 

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

문제

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.

해설

추출해야할 것 month, car_id, records<월별, 자동차별 대여 횟수>

조건 start_date BETWEEN "2022-08-01" AND "2022-10-31 ,  대여 횟수가 5 이상인 자동차들

정렬 month ASC, car_id DESC

 

1. 날짜 필터를 걸어준다 

2. 자동차별로 GROUP BY를 해주고 HAVING으로 COUNT(car_id) >=5인 car_id를 추출한다

------ 서브 쿼리 -------

3. 날짜필터를 메인쿼리에 걸어준다

4. 2번을 서브쿼리로 만들어주고 추출된 car_id에 포함된 자동차만 불러와준다

5. 월별, 자동차별 대여 횟수를 구해준다

6. 조건에 맞게 정렬한다

 

WITH A AS (SELECT * FROM car_rental_company_rental_history
WHERE start_date BETWEEN "2022-08-01" AND "2022-10-31")


SELECT MONTH(start_date), car_id , COUNT(car_id) AS RECORDS
FROM A
WHERE car_id IN (SELECT car_id
FROM A
GROUP BY car_id
HAVING COUNT(car_id) >= 5)
GROUP BY MONTH(start_date), car_id
ORDER BY MONTH(start_date) ASC, car_id DESC

※ 서브쿼리나 메인쿼리에 동일한 날짜 제약이 걸려있어서 

WITH을 통해 가상 테이블을 만들어준뒤 뽑아서 썼다.

 

 

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

해설

추출해야할 것 history_id, FEE

조건 "트럭"이면서 discount_plan 테이블의 정책에 맞게 할인율 적용하기

정렬 FEE DESC, history_id DESC

 

1. discount_plan 테이블과 JOIN을 하기위해 history 테이블의 날짜 차이를 이용해 duration_type 필드를 만들어준다.

-> CASE WHEN을 통해서 만들어주자

2. history 테이블과 car 테이블을 car_id로 조인해준다 

-> 왜냐하면 같은 차종이라도 daily_fee가 다르다

3. discount_plan 테이블과 car , history 테이블을 각각 car_type, duration_type으로 조인해준다 

-> 이때 나는 LEFT JOIN을 썼는데 history의 정보가 보존되어야하기 때문이다.

-> INNER JOIN을 쓰면 7일 이하의 정보들이 duration_type으로 조인되면서 사라진다

4. 트럭만 갖고와야하니 car 테이블의 트럭으로 WHERE 조건을 붙여준다

5. history_id와 FEE를 계산하는 SELECT 쿼리를 써준다

-> discount_rate의 7일 이하의 데이터는 LEFT JOIN으로 인해 공백인 상태인데

할인율이 적용되지 않은 채로 계산이 되어야한다.

따라서 IFNULL 함수를 써서 아래와 같이 써주면 된다

5. 조건에 맞게 정렬한다

SELECT history_id, ROUND((daily_fee * (diff+1) * (1-IFNULL(discount_rate * 0.01,0))),0) AS FEE
FROM 
(SELECT history_id, car_id, DATEDIFF(end_date, start_date) AS diff ,
(CASE 
WHEN DATEDIFF(end_date, start_date) BETWEEN 7 AND 29 THEN "7일 이상"
WHEN DATEDIFF(end_date, start_date) BETWEEN 30 AND 89 THEN "30일 이상"
WHEN DATEDIFF(end_date, start_date) >= 90 THEN "90일 이상"
ELSE "0일 이상"
END) AS duration_type
FROM car_rental_company_rental_history) AS h
INNER JOIN car_rental_company_car AS c
ON h.car_id = c.car_id
LEFT JOIN car_rental_company_discount_plan AS p
ON c.car_type = p.car_type AND p.duration_type = h.duration_type
WHERE c.car_type = '트럭'
ORDER BY FEE DESC, history_id DESC