TIL_[SQL] 코딩테스트 회고를 통한 sql 문법 공부8

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

Q. CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과
CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중
2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 
자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 
결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 
자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

문제는 점점 어려워지고,,
난 다른 사람의 풀이마저 이해하지 못하는 상황에 이르렀다.

정답 코드가 점점 길어지고 있는데 사람들은 이 복잡한 과정을 어떻게 풀이해나가는지 너무 궁금하다.

(정답)
with car_list as (
    select car_id, car_type, daily_fee
    from car_rental_company_car
    where car_id not in (
        select distinct car_id
        from car_rental_company_rental_history
        where end_date >= '2022-11-01')
    and car_type in ('세단', 'SUV')
), car_plan_30 as (
    select car_id, c.car_type, daily_fee, p.discount_rate
    from car_list as c
    left join car_rental_company_discount_plan as p 
    on c.car_type=p.car_type
    where p.duration_type = '30일 이상'
)

select car_id, car_type, 
        round(daily_fee*(1-0.01*discount_rate))*30 as FEE
from car_plan_30
where (daily_fee*(1-0.01*discount_rate))*30 between 500000 and 2000000
order by FEE desc, car_type, car_id

1. 30일간의 대여금액 계산하기 (FEE)
2. FEE의 범위 where 절에 지정하기
3. 2022-11-01~2022-11-30 대여가능한 자동차 찾는 법: NOT IN >= 2022-11-01 활용

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

Q.USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 
상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 
년, 월 별로 출력하는 SQL문을 작성해주세요. 
상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

(정답)
select year(sales_date) "YEAR", 
        month(sales_date) "MONTH",
        count(distinct user_id) "PUCHASED_USERS",
        round(count(distinct user_id)/(select count(*) from user_info where year(joined) = '2021'),1) AS PUCHASED_RATIO
from online_sale
where user_id in (select user_id
                  from user_info
                  where joined like '2021-%')
group by 1, 2
order by 1, 2

1. 한 아이디로 여러개 상품을 구매할 수 있음 -> distinct user_id 로 중복 회원 거르기
2. join 하지 않고도 where절 서브쿼리로 활용할 수 있음