SQL 프로그래머스 코딩 테스트 LEVEL 4 ~ 5

보호소에서 중성화한 동물

# 1. ANIMAL_INS와 ANIMAL_OUTS를 조인
# 2. 보호소 들올때 중성화 x AND 보호소 나갈때 중성화 O 
# 3. 동물 아이디, 생물종, 이름 조회
# 4. 아이디 순으로 조회

select animal_ins.animal_id, animal_ins.animal_type,animal_ins.name
from animal_ins
left join animal_outs on
animal_ins.animal_id=animal_outs.animal_id
where animal_ins.sex_upon_intake not like '%Neutered%' and animal_ins.sex_upon_intake not like '%Spayed%'
    and ( animal_outs.sex_upon_outcome like '%Neutered%' or animal_outs.sex_upon_outcome like '%Spayed%')
order by animal_ins.animal_id    
    

무난했던 문제

• 5월 식품들의 총 매출

-- 코드를 입력하세요
# 1. food product와 food order 테이블 조인
# 2. 2022년 5월인 식품
# 3. 해당 식품의 식품 id, 식품 이름, 총매출 조회
# 3-1. 총매출 테이블 필요
# 4. 총매출 기준 내림차순, 같으면 식품 id 기준 오름차순

생각과는 달리 정답이 아니었다.....
아 놓친게 하나 있었다....

같은 식품을 group by를 통해 그룹화 해서 묶어줘야한다..

수정 결과

정답 코드
select food_product.PRODUCT_ID, food_product.PRODUCT_NAME, sum(food_product.price * food_order.amount) as TOTAL_SALES
    from food_order
right join food_product on
food_order.product_id = food_product.product_id
where food_order.produce_date like '%2022-05%'
GROUP BY food_product.product_id
ORDER BY TOTAL_SALES DESC, food_order.product_id asc

• 식품분류별 가장 비싼 식품의 정보 조회하기

with table1 as(
    select category,max(price) as max_price from food_product
    where category in ('과자','국','김치','식용유')
    group by category
)
select food_product.category,table1.max_price as MAX_PRICE, food_product.product_name from food_product
left join table1 on
food_product.category = table1.category
where table1.category is not NULL and table1.max_price = food_product.price
order by food_product.price desc

in 연산자와 최대값 테이블 생성 하면 쉽게 풀 수 있다.

• 우유와 요거트가 담긴 장바구니
  

그룹화 하였을 때 그룹화 한 내용에 직접 접근해 제어 할 순 없을까?
구글링 결과 group_concat()이라는 함수를 발견하였다.

with table1 as (
select cart_id,group_concat(name) as name_group from cart_products
group by cart_id
)
select table1.cart_id as CART_ID from table1
where table1.name_group like '%Milk%' and table1.name_group like '%Yogurt%'

의외로 결과가 잘 나왔다!!

• 년, 월, 성별 별 구매 회원수 구하기

실패 코드
with table1 as(
    select online_sale.*, user_info.gender from online_sale 
    left join user_info on 
    online_sale.user_id = user_info.user_id
    where user_info.gender is not NULL
)
SELECT year(table1.sales_date) as YEAR,month(table1.sales_date) AS MONTH,
    table1.gender AS GENDER,COUNT(table1.user_id) AS USERS FROM table1
group by year(table1.sales_date),month(table1.sales_date),table1.gender
order by year(table1.sales_date),month(table1.sales_date),table1.gender

내가 뭔가 놓친게 있는건가? 왜 정답이 아니지?

회원수를 구하는 것이니 distinct로 중복을 제거해야 하지 않을까?

해결!!!!

정답 코드
with table1 as(
    select online_sale.*, user_info.gender from online_sale 
    left join user_info on 
    online_sale.user_id = user_info.user_id
    where user_info.gender is not NULL
)
SELECT year(table1.sales_date) as YEAR,month(table1.sales_date) AS MONTH,
    table1.gender AS GENDER,COUNT(distinct table1.user_id) AS USERS FROM table1
group by year(table1.sales_date),month(table1.sales_date),table1.gender
order by year(table1.sales_date),month(table1.sales_date),table1.gender

• 서울에 위치한 식당 목록 출력하기

# 1. REST_INFO 와 REST_REVIEW join O
# 2. 서울에 위치한 식당 찾기 O
# 3. 식당 id, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수 출력 
# 4. 리뷰 평균 점수 소수점 세번째 자리 반올림 round ( x, 2) 
# 5. 평균 점수 기준 내림차순 정렬, 즐겨찾기 수 기준 내림차순 정렬

select rest_info.rest_id,rest_info.rest_name,rest_info.food_type,
    rest_info.favorites,rest_info.address,round(avg(rest_review.review_score),2) as SCORE from rest_review
left join rest_info on
rest_review.rest_id = rest_info.rest_id
where address like '서울%'
group by(rest_info.rest_id)
order by SCORE desc, favorites desc

생각보다 무난했던 문제였다
avg함수와 round 함수를 잘 사용할 수 있는지가 핵심이다!!

• 취소되지 않은 진료 예약 조회하기

1차 시도
-- 코드를 입력하세요
# appointment.pt_no - patient.pt_no appointment.mddr_id - doctor.dr_id
# 1. 3 테이블 전부 조인
# 2. 2022년 4월 13일 
# 3. 예약 취소되지 않은 
# 4. 흉부외과(CS) 
# 5. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 출력

with table1 as(
    SELECT appointment.*,patient.pt_name,patient.gend_cd,patient.age,patient.tlno
        from appointment 
    left join patient on
    appointment.pt_no =patient.pt_no
), table2 as(
    select table1.*,doctor.dr_name,doctor.lcns_no,doctor.hire_ymd,doctor.mcdp_cd as dr_mcdp_cd,doctor.tlno as dr_tlno
        from table1
    left join doctor on 
    table1.mddr_id = doctor.dr_id
)
select apnt_no,pt_name,pt_no,mcdp_cd,dr_name,apnt_ymd from table2
where apnt_ymd like '2022-04-13%' and apnt_cncl_yn = 'N' and mcdp_cd='CS'
order by apnt_no asc

오답??? 어째서?
APNT_NO가 아니라 APNT_YMD 진료예약 일시기준으로 정렬하는 것이었다.

해결!! 
정답 코드
-- 코드를 입력하세요
# appointment.pt_no - patient.pt_no appointment.mddr_id - doctor.dr_id
# 1. 3 테이블 전부 조인
# 2. 2022년 4월 13일 
# 3. 예약 취소되지 않은 
# 4. 흉부외과(CS) 
# 5. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 출력

with table1 as(
    SELECT appointment.*,patient.pt_name from appointment 
    left join patient on
    appointment.pt_no =patient.pt_no
), table2 as(
    select table1.*,doctor.dr_name from table1
    left join doctor on 
    table1.mddr_id = doctor.dr_id
)
# select apnt_no,pt_name,pt_no,mcdp_cd,dr_name, apnt_ymd from table2
select apnt_no,pt_name,pt_no,mcdp_cd,dr_name, apnt_ymd from table2
where apnt_ymd like '2022-04-13%' and apnt_cncl_yn = 'N' and mcdp_cd='CS'
order by apnt_YMD asc

• 주문량이 많은 아이스크림들 조회하기
  상반기? 7월? 우선 테이블을 확인해 보아야겠다..
  
  

상반기와 7월은 서로 안 겹치는 것으로 보인다
-> 각각 테이블을 union으로 수직으로 합친후 group by로 flavor을 기준으로 그룹화,
이후 sum을 이용하여 테이블 생성
해당 테이블 order by total order 한 후 limit 3 하면 되지 않을까?

정답 코드 
-- 코드를 입력하세요
# 상반기1~6?? 7월? ???
# 1. 각각 테이블을 union으로 수직으로 조인
# 2. group by로 flavor을 기준으로 그룹화, 
# 3. 이후 sum을 이용하여 테이블 생성
# 4. 해당 테이블 order by total order 한 후 
# 5. limit 3 하면 되지 않을까?
with table1 as(
    (
        SELECT * FROM first_half
    )
    union
    (
        select * from july
    )
)
select FLAVOR from table1
group by flavor
order by sum(total_order) desc
limit 3

1트만에 성공!!!

• 저자 별 카테고리 별 매출액 집계하기

book_sales에는 book과 조인할 컬럼, 키는 있엇지만 author과 조인할 컬럼은 없었다. 하지만 book에는 book_sales와 author 둘다 조인할 키가 있다.
그러니 book을 기준으로 book_sales와 author 테이블을 조인하면 되지 않을까?

1트 만에 성공
정답 코드!!!
# BOOK = 각 도서 정보 테이블
# AUTHOR = 저자의 정보 테이블
# BOOK_SALES = 도서의 판매량 테이블
# 그럼 BOOK_SALES 테이블을 기반으로 BOOK과 AUTHOR을 조인하면 되지 않을까?

# 1. 2022년 1월 도서 판매데이터 기준? = 1월 도서만 찾으라는걸까?
# 2. 저자 별 카테고리 별 매출액 구할것? = GROUP BY로 저자별, 카테고리별? 매출액 구하는건가?
# 3. 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력
# 3-1. TOTAL_SALES = 판매량 * 판매가
# 4. 저자 ID 오름차순, 같다면 카테고리를 내림차순 정렬

with table1 as (
    select book.author_id,book.category,(book.price * book_sales.sales) as TOTAL_SALES,book_sales.sales_date
        from book
    left join book_sales on
    book.book_id = book_sales.book_id
    where book_sales.sales_date like '2022-01%'
    
), table2 as (
    select table1.*,author.author_name from table1
    left join author on 
    author.author_id = table1.author_id
)
select AUTHOR_ID,AUTHOR_NAME,CATEGORY,SUM(TOTAL_SALES) AS TOTAL_SALES from table2
group by AUTHOR_NAME, CATEGORY
order by author_id asc , category desc

• 그룹별 조건에 맞는 식당 목록 출력하기
  

회원 이름, 리뷰 텍스트, 리뷰 작성일 테이블 생성

1트 만에 성공!!
-- 코드를 입력하세요
# member_profile = 고객의 정보를 담은 테이블
# rest_review = 식당의 리뷰 정보를 담은 테이블
# rest_review 테이블을 중점으로 member_profile을 rest_review를 조인하면 되지 않을까?

# 1. 리뷰를 가장 많이 작성한 회원의 리뷰 조회
# 2. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력 ( 테이블 생성 ) 
# 2-1. 2의 테이블을 기반으로 가장 많이 리뷰 쓴 사람 테이블 생성
# 3. 리뷰 작성일을 기준으로 오름차순, 리뷰 텍스트를 기준으로 오름차순


with table1 as (
    select member_profile.member_name,rest_review.review_text,rest_review.review_date from rest_review
    left join member_profile on
    rest_review.member_id = member_profile.member_id
    # 회원 이름, 리뷰 텍스트, 리뷰 작성일 테이블
), table2 as(
    select member_name,count(*) as review_count from table1
    group by member_name
    order by review_count desc
    limit 1
    # 가장 많이 리뷰 쓴 회원 저장 테이블 Q 그럼 동일한 리뷰수의 회원은?
)
select table2.MEMBER_NAME, table1.REVIEW_TEXT, substring(table1.review_date,1,10) as REVIEW_DATE from table1
left join table2 on
table1.member_name = table2.member_name
where table2.member_name is not NULL
order by table1.review_date asc, table1.review_text asc
# 테이블 1과 테이블 2를 조인한 후 가장 많은 리뷰 쓴 회원 정보 출력

• 입양 시각 구하기(2)

# ANIMAL_OUTS  단일 테이블
# 1. 목적  몇 시에 입양이 가장 활발하게 일어나는지 
# 2. 0시부터 23시까지, 각 시간대별로 ( GROUP BY )
# 3. 입양이 몇 건이나 발생했는지 ( SUM ) 조회
# 4. 결과는 시간대 순으로 정렬

입양을 하지 않은 시간대가 출력되지 않는다.
시간을 나타내는 테이블이 더 필요하다!!
0 ~ 23까지 출력하는 테이블 말이다!!
우선 select 문과 union을 이용해서 0 ~ 23 까지 테이블을 만들면 어떨까?

-- 코드를 입력하세요
# ANIMAL_OUTS  단일 테이블
# 1. 목적  몇 시에 입양이 가장 활발하게 일어나는지 
# 2. 0시부터 23시까지, 각 시간대별로 ( GROUP BY ), 입양이 없더라도? 시간 표시?
# 2-1. sql 시간 추출 함수 목록 : https://extbrain.tistory.com/60
# 3. 입양이 몇 건이나 발생했는지 ( SUM ) 조회
# 4. 결과는 시간대 순으로 정렬

with table1 as (
    SELECT hour(datetime) as hour, count(*) as animal_adopt_count FROM animal_outs
    group by hour
    order by hour
), table2 as (
    select 0 as num union select 1 as num union select 2 as num union select 3 as num union select 4 as num union 
    select 5 as num union select 6 as num union select 7 as num union select 8 as num union select 9 as num union 
    select 10 as num union select 11 as num union select 12 as num union select 13 as num union select 14 as num union 
    select 15 as num union select 16 as num union select 17 as num union select 18 as num union select 19 as num union 
    select 20 as num union select 21 as num union select 22 as num union select 23 as num 
), table3 as (
    select * from table2
    left join table1 on
    table1.hour = table2.num
)
select num as HOUR, 
(
    case when animal_adopt_count is NULL then 0
    else animal_adopt_count end
) as COUNT
from table3
order by num

정답으로 나오긴 했지만 너무 비 효율적이다.
뭔가 좀 더 효율적으로 할 수 있는 방법은 없을까?

구글링 결과 with recursive 절을 알게 되었다.

https://dncjf0223.tistory.com/59

with recursive 코드를 실행해 보았지만 아래와 같이 에로가 발생하였다

원인을 파악해본 결과 with recursive 는 가장 위에 작성해야하고 해당 절은 그 아래 쓰인 테이블 들이 전부 재귀적으로 사용할 수 있다는 것을 의미한다.
아래와 같이 코드를 수정하자 에러는 사라지고
정답으로 나왔다!!

정답 코드
-- 코드를 입력하세요
# ANIMAL_OUTS  단일 테이블
# 1. 목적  몇 시에 입양이 가장 활발하게 일어나는지 
# 2. 0시부터 23시까지, 각 시간대별로 ( GROUP BY ), 입양이 없더라도? 시간 표시?
# 2-1. sql 시간 추출 함수 목록 : https://extbrain.tistory.com/60
# 3. 입양이 몇 건이나 발생했는지 ( SUM ) 조회
# 4. 결과는 시간대 순으로 정렬

with recursive table1 as (
    SELECT hour(datetime) as hour, count(*) as animal_adopt_count FROM animal_outs
    group by hour
    order by hour
),
 table2 as (
    select 0 as num
    union all
    select num+1  
    from table2
    where num < 23
    
),

table3 as (
    select * from table2
    left join table1 on
    table1.hour = table2.num
)
select num as HOUR, 
(
    case when animal_adopt_count is NULL then 0
    else animal_adopt_count end
) as COUNT
from table3
order by num

• 오프라인/온라인 판매 데이터 통합하기

![](https://velog.velcdn.com/images/songyuheon/post/44700b8b-d40c-41da-8ddb-9424c5476279/image.png)

뭐가 문제지?
혹시 NULL을 문자열이 아니라 NULL로 출력해야 되는 건가?

해결 ....

정답 코드 
# online_sale = 쇼핑몰 온라인 상품 판매 정보 테이블
# offline_sale = 오프라인 상품 판매 정보 테이블
# 1. ONLINE_SALE 테이블과 OFFLINE_SALE 테이블 조인
# 1-1. 테이블들과 최종 출력 쿼리들을 보니 join이 아닌 union으로 합쳐야할 것 같다.
# 1-2. online 테이블은 SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT 
# 1-3. offline은 SALES_DATE, PRODUCT_ID, NULL as USER_ID, SALES_AMOUNT 로 하면 될것 같다
# 2. 2022년 3월의 오프라인/온라인 상품 판매 데이터
# 3. 판매 날짜, 상품ID, 유저ID, 판매량을 출력
# 4. OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시
# 5. 판매일을 기준으로 오름차순 정렬, 상품 ID를 기준으로 오름차순, 유저 ID를 기준으로 오름차순 정렬
with table1 as(
    select substring(SALES_DATE,1,10) as SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT  from online_sale
    union
    select substring(SALES_DATE,1,10) as SALES_DATE, PRODUCT_ID, NULL as USER_ID, SALES_AMOUNT  from offline_sale
)
select * from table1
where sales_date like '2022-03%'
order by sales_date asc, product_id asc, user_id asc

• 상품을 구매한 회원 비율 구하기
  

정답 코드
# user_info = 의류 쇼핑몰에 가입한 회원 정보 테이블
# online_sale = 온라인 상품 판매 정보 테이블

# 1. online_sale 테이블 기반으로 user_info 조인
# 1-1. 전체 회원수 정보가 들어간 테이블과 구매한 회원수의 정보 테이블 필요
# 2. 2021년에 가입한 전체 회원들
# 3. 상품을 구매한 회원수와 상품을 구매한 회원의 비율(상품을 구매한 회원수 / 전체 회원 수)
# 4. 년, 월 별로 출력 ( group by ? )
# 5. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림 ( round(n,1))
# 5. 년을 기준으로 오름차순 정렬, 년이 같다면 월을 기준으로 오름차순 정렬
with table0 as (
    select count(*) as all_user_personnel  from user_info 
    where user_info.joined like '2021%'
    # 2021년 가입 전체 회원 테이블
), table1 as (
    # table1 -> table2 = 2021년 가입 회원 중 구매한 회원 테이블 월별 분리
    select month(online_sale.sales_date) as month_order_user,online_sale.user_id from online_sale
    left join user_info on
    online_sale.user_id = user_info.user_id
    where user_info.joined like '2021%'
    group by month_order_user, online_sale.user_id
    order by month_order_user asc
), table2 as(
    select month_order_user, count(*) as all_month_user_personnel from table1
    group by month_order_user
    
), table3 as (
    select online_sale.sales_date,online_sale.sales_amount from online_sale
    left join user_info on
    online_sale.user_id = user_info.user_id
    where user_info.joined like '2021%'
    # online_sale과 user_info를 조인한 sales_date, sales_amount 정보를 가지고 있는 테이블
), table4 as (
    select * from table3,table0
    # table3과 table0를 수평으로 합침
), table5 as (
    select * from table4
    left join table2 on 
    month(table4.sales_date)=table2.month_order_user
)
select year(sales_date) as YEAR, month(sales_date) as MONTH, all_month_user_personnel as PUCHASED_USERS,
    round(all_month_user_personnel/all_user_personnel,1) as PUCHASED_RATIO 
    from table5
group by YEAR, MONTH
order by YEAR asc, MONTH asc

길었다....

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

-- 코드를 입력하세요
# car_rental_company_car = 자동차 대여 회사의 대여중인 자동차 정보 테이블
# car_rental_company_history = 자동차 대여 기록 정보 테이블
# car_rental_company_discount_plan = 자동차 종류 별 대여기간 종류 별 할인 정책 정보 테이블

# 1. 자동차 종류가 '세단' 또는 'SUV' 인 자동차 O
# 2. 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능 O
# 3. 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차 O
# 4. 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력 
# 5. 대여 금액 기준 내림차순, 자동차 종류를 기준 오름차순 정렬, 자동차 ID를 기준 내림차순 정렬


with table1 as (
    select car.car_id,car.daily_fee,discount.discount_rate
     ,(1-(discount.discount_rate/100))*car.daily_fee*30 as month_fee
    
    from car_rental_company_car as car
    left join CAR_RENTAL_COMPANY_DISCOUNT_PLAN as discount on
    car.car_type =discount.car_type 
    where discount.duration_type like '30%'
), table2 as (
select car_id,month_fee from table1
    where month_fee <2000000 and month_fee>=500000
), table3 as (
select car.car_id,car.car_type from car_rental_company_car as car
where car.car_type in ('세단','SUV') and
    car.car_id in (
        select distinct car_id
            from car_rental_company_rental_history as history
        where (substring(history.start_date,1,10) < '2022-11-01' 
                    and substring(history.end_date,1,10) < '2022-11-01' ) or
              (substring(history.start_date,1,10) > '2022-11-30' 
                    and substring(history.end_date,1,10) > '2022-11-30' ) 
        order by car_id
    ) and 
    car.car_id in (
        select car_id from table2   
    )
), table4 as (    
    select table3.car_id as CAR_ID,table3.car_type as CAR_TYPE
            ,round(table2.month_fee) as FEE 
        from table3
    left join table2 on
    table3.car_id = table2.car_id
)
select * from table4
order by fee desc, car_type asc, car_id desc

최대한 테이블을 늘리지 않고 코드를 줄여 보려했지만.... 코드 길이 역시 길고 정답 또한 아니다..

원인을 파악하다 보니 한 가지를 발견했다

CAR_ID가 18인 것은 제외되야 하지만 제외되지 않았다 원인이 뭘까?

수많은 대여기록 중 제외되지 않는 중복되는 것이 있기 때문이다

따라서 in 방식이 아닌 not in 형태로 기존 코드를 변형하면 될 것 같다!!

드디어 풀었다 !! 
정답코드
# car_rental_company_car = 자동차 대여 회사의 대여중인 자동차 정보 테이블
# car_rental_company_history = 자동차 대여 기록 정보 테이블
# car_rental_company_discount_plan = 자동차 종류 별 대여기간 종류 별 할인 정책 정보 테이블

# 1. 자동차 종류가 '세단' 또는 'SUV' 인 자동차 O
# 2. 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능 O
# 3. 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차 O
# 4. 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력 
# 5. 대여 금액 기준 내림차순, 자동차 종류를 기준 오름차순 정렬, 자동차 ID를 기준 내림차순 정렬


with table1 as (
    select car.car_id,car.daily_fee,discount.discount_rate
     ,(1-(discount.discount_rate/100))*car.daily_fee*30 as month_fee
    
    from car_rental_company_car as car
    left join CAR_RENTAL_COMPANY_DISCOUNT_PLAN as discount on
    car.car_type =discount.car_type 
    where discount.duration_type like '30%'
), 

table2 as (
select car_id,month_fee from table1
    where month_fee <=2000000 and month_fee>=500000
), 

table3 as (
select car.car_id,car.car_type from car_rental_company_car as car
where car.car_type in ('세단','SUV') and
    car.car_id not in (
        select car_id
            from car_rental_company_rental_history as history
        where !((substring(history.start_date,1,10) < '2022-11-01' 
                    and substring(history.end_date,1,10) < '2022-11-01' ) or
              (substring(history.start_date,1,10) > '2022-11-30' 
                    and substring(history.end_date,1,10) > '2022-11-30' ) )
        order by car_id
    ) and 
    car.car_id in (
        select car_id from table2   
    )
), 
table4 as (    
    select table3.car_id as CAR_ID,table3.car_type as CAR_TYPE
            ,floor(table2.month_fee) as FEE 
        from table3
    left join table2 on
    table3.car_id = table2.car_id
)
select CAR_ID,CAR_TYPE,FEE from table4
order by fee desc, car_type asc, car_id desc

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

-- 코드를 입력하세요
# CAR_RENTAL_COMPANY_CAR = 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 테이블
# CAR_RENTAL_COMPANY_RENTAL_HISTORY = 자동차 대여 기록 정보를 담은 테이블
# CAR_RENTAL_COMPANY_DISCOUNT_PLAN = 자동차 종류 별 대여 기간 종류 별 할인 정책 정보를 담은 테이블

# 1. 자동차 종류가 '트럭'인 자동차 
# 2. 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 
# 3. 대여 기록 ID와 대여 금액 리스트를 출력
# 4. 대여 금액을 기준 내림차순 정렬, 대여 기록 ID를 기준 내림차순 정렬
with date_term as(
    select history.history_id,history.car_id,car.car_type,datediff(end_date,start_date)+1 as diff_date 
        ,(
            case when datediff(end_date,start_date)+1<7 then 0
            when datediff(end_date,start_date)+1<30 then 7
            when datediff(end_date,start_date)+1<90 then 30
            else 90 end
        ) as discount_day_type 
    , car.daily_fee
    from CAR_RENTAL_COMPANY_RENTAL_HISTORY history
    left join CAR_RENTAL_COMPANY_CAR car on
    car.car_id = history.car_id 
    where car.car_type = '트럭'
), discount_car as (
    select (
            case when duration_type like '7%' then 7
            when duration_type like '30%' then 30
            else 90  end
        ) as discount , discount_rate
        from CAR_RENTAL_COMPANY_DISCOUNT_PLAN plan
        where plan.car_type = '트럭'
) 
,full_table as (
    select date_term.*,
    (
        case when discount_car.discount_rate is NULL then 0
        else discount_car.discount_rate end
    ) as discount_rate    
     from date_term
    left join discount_car on
    date_term.discount_day_type = discount_car.discount
)
select history_id as HISTORY_ID, 
floor((
    case when discount_rate = 0 then daily_fee*diff_date
    else (1-(discount_rate/100))*daily_fee*diff_date end
)) as FEE
from full_table
order by FEE desc,history_id desc

정말 길었다 최대한 실수없이 1트만에 하고 싶은 마음에 하나 하나 데이터를 확인하며 마무리 했다
시간이 많이 걸렸지만 마지막까지 마무리 해서 그런지 보람차다!!!!