코드카타

---

코드카타

특징 기간동안 대여 가능한 자동차들의 대여비용 구하기
자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일 부터 2022년 11월 30일까지 대여가능한 자동차는 자동차 ID가 1, 4, 5인 자동차입니다.
일일 대여 요금에 자동차 종류 별 대여기간이 30일 이상인 경우의 할인율을 적용하여 30일간의 대여 금액을 구하면,
자동차 ID가 1인 경우,
일일 대여 금액 25,000원에서 8% 할인율을 적용하고 30일을 곱하면 총 대여 금액은 690,000원자동차 ID가 4인 경우,
일일 대여 금액 12,000원에서 10% 할인율을 적용하고 30일을 곱하면 총 대여 금액은 324,000원자동차 ID가 5인 경우, 일일 대여 금액 22,000원에서 10% 할인율을 적용하고 30일을 곱하면 총 대여 금액은 621,000원이고,
대여 금액이 50만원 이상 200만원 미만인 경우에 대해서 대여 금액을 기준으로 내림차순, 자동차 종류를 기준으로 오름차순 및 자동차 ID를 기준으로 내림차순 정렬하면 다음과 같아야 합니다.

• 정수 : FLOOR
• 자동차 종류가 '세단' 또는 'SUV'  : WHERE IN
• 대여기간이 30일 이상 : = '30일 이상'
• 자동차 ID가 1, 4, 5 : GROUP BY
• 2022년 11월 1일 부터 2022년 11월 30일까지 대여가능한 자동차 : HAVING
• 대여 금액이 50만원 이상 200만원 미만 : FEE >= 50만원, FEE < 200만원
• 내림차순 정렬 : ORDER BY

select c.car_id, c.car_type, floor(c.daily_fee * 30 * (1-p.discount_rate*0.01)) as fee
from car_rental_company_car c, car_rental_company_rental_history h, car_rental_company_discount_plan p
where c.car_id = h.car_id
and c.car_type = p.car_type
and c.car_type in ('세단', 'SUV')
and p.duration_type = '30일 이상'
group by c.car_id
having max(h.end_date) < '2022-11-01'
and fee >= 500000 and fee < 2000000
order by fee desc, c.car_type, c.car_id desc;

 

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

• 자동차 종류가 '트럭' : WHERE = 트럭
• 대여 금액 리스트 : END DATE - START DATE -> DATEDIFF
• 대여 금액 리스트 : CASE WHEN BETWEEN A AND B
• 대여 기록 별로 대여 금액 : 요금 * 대여기록 리스트 * 100 - 할인율 * 0.01
• 정렬 : ORDER BY

SELECT DISTINCT HISTORY_ID, FEE
FROM
    (SELECT H.history_id as HISTORY_ID,
       DATEDIFF(end_date, start_date) AS 'DATE_DIFF',
           CASE 
                WHEN (DATEDIFF(end_date, start_date)+1 BETWEEN 7 AND 29) AND duration_type = '7일 이상' THEN ROUND(daily_fee * (DATEDIFF(end_date, start_date)+1) * (100 - discount_rate) * 0.01)
                WHEN (DATEDIFF(end_date, start_date)+1 BETWEEN 30 AND 89) AND duration_type = '30일 이상' THEN ROUND(daily_fee * (DATEDIFF(end_date, start_date)+1) * (100 - discount_rate) * 0.01)
                WHEN (DATEDIFF(end_date, start_date)+1 >= 90) AND duration_type = '90일 이상' THEN ROUND(daily_fee *(DATEDIFF(end_date, start_date)+1) * (100 - discount_rate) * 0.01)
                WHEN (DATEDIFF(end_date, start_date) < 7)  THEN ROUND(daily_fee * (DATEDIFF(end_date, start_date)+1))
                ELSE NULL
           END AS 'FEE'
    FROM CAR_RENTAL_COMPANY_CAR AS C
         LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H ON C.car_id = H.car_id
         LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P ON C.car_type = P.car_type
    WHERE C.car_type = '트럭') AS A
WHERE FEE IS NOT NULL
ORDER BY FEE DESC, history_id DESC;

 

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

• 년, 월 별로 출력 : YEAR, MONTH
• 2021년에 가입한 전체 회원 수 : 서브쿼리
• 상품을 구매한 회원의 비율 : 2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수
• 회원의 비율은 소수점 두번째자리에서 반올림 : ROUND
• 년, 월 별 : GROUP BY
• 오름차순 정렬 : ORDER BY

SELECT YEAR(SALES_DATE) AS YEAR ,  MONTH(SALES_DATE) AS MONTH, COUNT(DISTINCT USER_ID) AS 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 YEAR(JOINED) = "2021")
GROUP BY YEAR(SALES_DATE) , MONTH(SALES_DATE)
ORDER BY YEAR , MONTH;