[데이터분석] 코드카타 (Lv.5~ Lv.6)

74. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (버림문제)

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를 기준으로 내림차순 정렬해주세요.

 

(오답)

select a.car_id,
       a.car_type,
       round(a.daily_fee*30*(100-c.discount_rate)/100) 'fee' 
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_id not in
(
select car_id
    from car_rental_company_rental_history
    where start_date >= '2022-11-01' and end_date <= '2022-11-30'
) and c.duration_type like '30%'
group by a.car_id
having a.car_type in ('세단','SUV') and (fee>=500000 and fee <=2000000)
order by 3 desc, 1 desc

 

SELECT C.CAR_ID  CAR_ID,

              C.CAR_TYPE as CAR_TYPE, 
             -- 대여료 x 30일 x 내야할 비율 = 요금
             -- (100 - 할인률)/100 = 내야할 비율
              ROUND(C.DAILY_FEE*30*(100-P.DISCOUNT_RATE)/100) AS FEE -- 지불 비용
              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            
    -- 대여 가능한 2022-11-01 ~ 2022-12-01에 대여가 가능한 자동차를 목록을 가져와야하므로 
    -- NOT IN을 써서 해당 기간에 렌탈 기록이 없는 CAR_ID를 가져와야 한다
    WHERE C.CAR_ID NOT IN ( 
        SELECT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE END_DATE >= '2022-11-01' AND START_DATE <= '2022-12-01'
        
) AND P.DURATION_TYPE like '30%' 
GROUP BY C.CAR_ID
HAVING C.CAR_TYPE IN ('세단', 'SUV') 
    AND (FEE >= 500000 AND FEE < 2000000) -- 30일간의 대여 금액이 50만원 200만원 미만인 자동차 
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC

 

 

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

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

 

WITH value AS (
    SELECT car.daily_fee, car.car_type, his.history_id,
           DATEDIFF(end_date, start_date) + 1 AS period,
    CASE 
      WHEN DATEDIFF(end_date, start_date) + 1 >= 90 THEN '90일 이상'
      WHEN DATEDIFF(end_date, start_date) + 1 >= 30 THEN '30일 이상'
      WHEN DATEDIFF(end_date, start_date) + 1 >= 7 THEN '7일 이상'
      ELSE 'NONE' END AS duration_type
FROM car_rental_company_rental_history AS his
INNER JOIN car_rental_company_car AS car ON car.car_id = his.car_id
WHERE car.car_type = '트럭')   

SELECT value.history_id, 
    ROUND(value.daily_fee * value.period * 
          (100 - IFNULL(plan.discount_rate,0)) / 100) AS FEE
FROM value
LEFT JOIN car_rental_company_discount_plan AS plan 
    ON plan.duration_type = value.duration_type 
    AND plan.car_type = value.car_type
ORDER BY 2 DESC, 1 DESC

 

 

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

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

 

SELECT DATE_FORMAT(O.SALES_DATE, '%Y') AS YEAR,
              DATE_FORMAT(O.SALES_DATE, '%m') AS MONTH,
        COUNT(DISTINCT U.USER_ID) AS PUCHASED_USERS,
        ROUND(COUNT(DISTINCT U.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE joined LIKE '2021%'), 1) AS PUCHASED_RATIO
FROM USER_INFO U
JOIN ONLINE_SALE O
ON U.USER_ID = O.USER_ID
WHERE U.JOINED LIKE '2021%'
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH