[SQL] 자동차 대여가능여부 찾기

사용한 문제는 3개로 모두 programmers에서 발췌함.

1. 2022년 8월~ 2022년 10월까지 총 대여 횟수가 5회이상인 자동차 찾기

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

start_date가 2022.08.01~2022.10.31 인 조건 추출 .

with time as (
select car_id, count(*) 
from car_rental_company_rental_history 
where start_date >= '2022-08-01' and start_date < '2022-11-01' 
group by car_id
having count(*) >4
)
select  month(h.start_date)as month , h.car_id, count(*) as records
from time t
join car_rental_company_rental_history h on h.car_id = t.car_id

group by month(h.start_date), car_id
order by month(h.start_date), car_id desc;

2.2022년 10월 16일에 대여중인 자동차를 표시하기

group by lv.3 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

start_date와 end_date 사이에 2022.10.16이 있으면 됨.

-- 최신순으로 업데이트 하고 뽑기 vs 있는거 셀렉하고 나중에 셀프조인
with available as (
SELECT car_id , '대여중' as availability
from car_rental_company_rental_history
where '2022-10-16' between start_date and end_date 
order by car_id desc)
select distinct c.car_id , ifnull(availability, '대여 가능')
from car_rental_company_rental_history  c
left join available a on c.car_id = a.car_id
order by c.car_id desc;

3. 2022.11~ 2022.11.30 에 대여중이지 않은 자동차 표시하기 (대여가능한)

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

• 1. where절에 서브쿼리로 2022.11~2022.11.30 대여중인 차량을 not in 처리로 빼줌.
• 2. cte만들고 left join, 하고 were car_id is null조건을 활용한다.

• 나는 with as구문 사용하는 것을 즐겨하기 때문에 2번 풀이의 leftjoin 후 null값 추출하는 방법을 익혀야 겠다.

-- 1.자동차 종류가 '세단', 'suv' 
-- 2.2022.11.1~ 2022.11.30 대여 가능 -> 대여불가 car_id 제외하기 (not in 안쓰고 어케함)
-- 3. 30일간의 대여금액이 50만~200만 

WITH rentalno AS (
    SELECT car_id
    FROM car_rental_company_rental_history 
    WHERE start_date < '2022-12-01' AND end_date > '2022-11-01'
),
cars AS (
    SELECT c.car_id, c.car_type, c.daily_fee
    FROM car_rental_company_car c
    LEFT JOIN rentalno ON rentalno.car_id = c.car_id
    WHERE c.car_type IN ('세단', 'SUV')  -- 여기서 'SUV'로 수정(대소문자 맞춤)
      AND rentalno.car_id IS NULL
),
final AS (
    SELECT c.car_id, c.car_type,
           ROUND(30 * (c.daily_fee * (100 - p.discount_rate) / 100), 0) AS fee
    FROM cars c 
    JOIN car_rental_company_discount_plan p ON c.car_type = p.car_type
    WHERE p.duration_type = '30일 이상'
)
SELECT car_id, car_type, fee 
FROM final 
WHERE fee BETWEEN 500000 AND 2000000
ORDER BY fee DESC, car_type, car_id;