TIL_[SQL] 코딩테스트 회고

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

Q. CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 
CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 
대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 
결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

어제부터 풀던 문제인데 죽어도 이해가 안가서 튜터님께 내가 쓴 코드 확인을 부탁드렸다 ㅠ

(내가 쓴 코드 = 오답)
select history_id,
    case when "일수" between 7 and 29 then round(daily_fee*"일수"*(100-discount_rate)/100)
        when "일수" between 30 and 89 then round(daily_fee*"일수"*(100-discount_rate)/100)
        when "일수" >= 90 then round(daily_fee*"일수"*(100-discount_rate)/100)
        else round(daily_fee*"일수") end "FEE"
from (
    select history_id,
            datediff(b.end_date, b.start_date) + 1 as "일수",
            a.daily_fee,
            c.discount_rate,
            c.duration_type
            from car_rental_company_car a join car_rental_company_rental_history b
            on a.car_id=b.car_id join car_rental_company_discount_plan c
            on a.car_type=c.car_type
            where a.car_type = '트럭'
) aa
group by 1
order by "FEE" desc, 1 desc

1. case when절의 조건값이 모두 똑같은 경우 case when을 쓸 필요가 없음
2. from절 서브쿼리만 따로 실행해보면, "일수" 와 duration_type이 매칭되지 않음
   • "일수" 와 duration_type이 일치되도록 join을 수정해볼것

나머지는 내일 오전시간에 위에 조언 주신 내용을 바탕으로 다시 코드를 수정할 예정이다.

아래는 다른 사람들이 푼 코드들인데 각각 자신들의 방식으로 해석하고 풀이하신게 대단하다고 느꼈다 🥲

참고가 될만한 것이나 새로운 함수정도만 공부하되, 남이 푼 코드내용을 복사 붙여넣기로 끝내지 않고 앞으로 스스로 문제해결을 할 수 있도록 충분히 생각해보고 내재화해서 나만의 것으로 만들 수 있도록 더 많이 공부해야겠다.

(남풀코 1.) = 남이 푼 코드
SELECT
HISTORY_ID,
ROUND(DAILY_FEE * (100 - IFNULL(DISCOUNT_RATE, 0)) / 100 * (DATEDIFF(END_DATE, START_DATE) + 1)) FEE
FROM CAR_RENTAL_COMPANY_CAR c
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h
ON c.CAR_ID = h.CAR_ID AND c.CAR_TYPE = "트럭"
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN p
ON c.CAR_TYPE = p.CAR_TYPE
AND DATEDIFF(END_DATE, START_DATE) + 1 >= CAST(p.DURATION_TYPE AS UNSIGNED)
GROUP BY HISTORY_ID
ORDER BY FEE DESC, HISTORY_ID DESC;

(남풀코 2.)
select distinct(history_id),
		case when datediff(end_date, start_date)+1 between 7 and 29 then round(daily_fee*(datediff(end_date, start_date)+1)*(select (100-DISCOUNT_RATE)/100 from CAR_RENTAL_COMPANY_DISCOUNT_PLAN where car_type = '트럭' and duration_type = '7일 이상'),0)
			 when datediff(end_date, start_date)+1 between 30 and 89 then round(daily_fee*(datediff(end_date, start_date)+1)*(select (100-DISCOUNT_RATE)/100 from CAR_RENTAL_COMPANY_DISCOUNT_PLAN where car_type = '트럭' and duration_type = '30일 이상'),0)
			 when datediff(end_date, start_date)+1 >=90 then round(daily_fee*(datediff(end_date, start_date)+1)*(select (100-DISCOUNT_RATE)/100 from CAR_RENTAL_COMPANY_DISCOUNT_PLAN where car_type = '트럭' and duration_type = '90일 이상'),0)
			 else round(daily_fee*(datediff(end_date, start_date)+1),0)
			end as fee
from CAR_RENTAL_COMPANY_RENTAL_HISTORY a
join CAR_RENTAL_COMPANY_CAR b
on a.CAR_ID = b.CAR_ID
join CAR_RENTAL_COMPANY_DISCOUNT_PLAN c
on b.CAR_TYPE = c.CAR_TYPE
where b.CAR_TYPE = '트럭'
order by 2 desc, 1 desc

(남풀코 3.)
select history_id
     , round(daily_fee  * (datediff(end_date, start_date) + 1) * coalesce(1 - (discount_rate * .01),1),0) as fee
from
(SELECT h.history_id, h.car_id, c.car_type, c.daily_fee, h.end_date, h.start_date,
        case when datediff(h.end_date, h.start_date) + 1 between 7 and 29 then '7일 이상'
             when datediff(h.end_date, h.start_date) + 1 between 30 and 89 then '30일 이상'
             when datediff(h.end_date, h.start_date) + 1 >= 90 then '90일 이상'
             else null end as duration_date
from CAR_RENTAL_COMPANY_RENTAL_HISTORY h
join CAR_RENTAL_COMPANY_CAR c on c.car_id = h.car_id) a
left join CAR_RENTAL_COMPANY_DISCOUNT_PLAN p on a.car_type = p.car_type and a.duration_date = p.duration_type
where a.car_type = '트럭'
order by fee desc, history_id desc;