[SQL문제풀기] 자동차 대여 기록 별 대여 금액 구하기

문제

MYSQL

내가 작성한 정답 1

with a as (select h.history_id, c.car_id, c.car_type, c.daily_fee, h.start_date,
                  h.end_date, datediff(end_date,start_date)+1 period,
                    case 
                        when datediff(end_date,start_date)+1 >= 90 then duration_type = '90일 이상' 
                        when datediff(end_date,start_date)+1 >= 30 then duration_type = '30일 이상'
                        when datediff(end_date,start_date)+1 >= 7 then duration_type = '7일 이상'
                        else 0 end as bool,
                    case 
                        when datediff(end_date,start_date)+1 < 7 then 0
                        else d.discount_rate end as discount        
            from CAR_RENTAL_COMPANY_CAR c
            join CAR_RENTAL_COMPANY_DISCOUNT_PLAN d on c.CAR_TYPE = d.CAR_TYPE
            join CAR_RENTAL_COMPANY_RENTAL_HISTORY h on c.car_id = h.car_id)
select history_id, round(period*(100-discount)*0.01*daily_fee) fee
from a 
where car_type = '트럭' and (bool = 1 or period < 7)
group by history_id, round(period*(100-discount)*0.01*daily_fee)
order by 2 desc, 1 desc

내가 작성한 정답 2

with a as (select h.history_id, c.car_id, c.car_type, c.daily_fee, d.discount_rate,
                    h.start_date, h.end_date, datediff(h.end_date,h.start_date)+1 period,
                    case 
                        when datediff(h.end_date,h.start_date)+1 >= 90 and (d.duration_type = '90일 이상') then d.discount_rate
                        when datediff(h.end_date,h.start_date)+1 >= 30 and (d.duration_type = '30일 이상') then d.discount_rate
                        when datediff(h.end_date,h.start_date)+1 >= 7 and (d.duration_type = '7일 이상') then d.discount_rate
                        else 0 end as discount      
            from CAR_RENTAL_COMPANY_CAR c
            join CAR_RENTAL_COMPANY_DISCOUNT_PLAN d on c.CAR_TYPE = d.CAR_TYPE
            join CAR_RENTAL_COMPANY_RENTAL_HISTORY h on c.car_id = h.car_id)
select history_id, min(round(period*(100-discount)*0.01*daily_fee)) fee 
from a 
where car_type = '트럭'
group by history_id
order by 2 desc, 1 desc

ORACLE

내가 작성한 정답 1

with a as (SELECT case 
            when ((h.end_date-h.start_date)+1 >= 90) and (d.duration_type='90일 이상') then discount_rate
            when ((h.end_date-h.start_date)+1 >= 30) and (d.duration_type='30일 이상') then discount_rate
            when ((h.end_date-h.start_date)+1 >= 7) and (d.duration_type='7일 이상') then discount_rate
            else 0      end as discount,
                 h.history_id, c.daily_fee, (h.end_date-h.start_date)+1 period
            from CAR_RENTAL_COMPANY_CAR c
            join CAR_RENTAL_COMPANY_RENTAL_HISTORY h on c.car_id = h.car_id
            join CAR_RENTAL_COMPANY_DISCOUNT_PLAN d on c.car_type = d.car_type
            where c.car_type ='트럭')
select history_id, round(daily_fee*period*(100-discount)*0.01) fee
from (select history_id, daily_fee, period, max(discount) discount 
      from a group by history_id, daily_fee, period)
order by fee desc, history_id desc

select history_id, min(round(period*(100-discount)*0.01*daily_fee)) fee 
from a
group by history_id
order by fee desc, history_id desc

내가 작성한 정답 2

with a as (SELECT 
            max(case 
                when ((h.end_date-h.start_date)+1 >= 90) and (d.duration_type='90일 이상') then discount_rate
                when ((h.end_date-h.start_date)+1 >= 30) and (d.duration_type='30일 이상') then discount_rate
                when ((h.end_date-h.start_date)+1 >= 7) and (d.duration_type='7일 이상') then discount_rate
                else 0      end) as discount,
       h.history_id, c.daily_fee, (h.end_date-h.start_date)+1 period
from CAR_RENTAL_COMPANY_CAR c
join CAR_RENTAL_COMPANY_RENTAL_HISTORY h on c.car_id = h.car_id
join CAR_RENTAL_COMPANY_DISCOUNT_PLAN d on c.car_type = d.car_type
where c.car_type ='트럭'
group by h.history_id, c.daily_fee, (h.end_date-h.start_date)+1 )
select history_id, daily_fee*period*(100-discount)*0.01 fee
from a
order by fee desc ,history_id desc