240806 TIL

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

프로그래머스

CAR_RENTAL_COMPANY_CAR 테이블

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블

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*(1-c.discount_rate/100)*30) 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 end_date >= '2022-11-01' and start_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, 2, 1 desc

---

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

프로그래머스

CAR_RENTAL_COMPANY_CAR 테이블

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블

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

 

• 풀이

with car as (
    SELECT a.car_type, a.daily_fee, b.history_id, 
        datediff(b.end_date, b.start_date)+1 period, 
    case when datediff(b.end_date, b.start_date)+1 >= 90 then '90일 이상'
    when datediff(b.end_date, b.start_date)+1 >= 30 then '30일 이상'
    when datediff(b.end_date, b.start_date)+1 >= 7 then '7일 이상'
    else 'none' end duration_type
FROM car_rental_company_car a 
JOIN car_rental_company_rental_history b 
ON a.car_id=b.car_id
where a.car_type = '트럭')


select c.history_id,
round(c.daily_fee*c.period*(1-ifnull(p.discount_rate,0)/100)) fee
from car c 
left join car_rental_company_discount_plan p 
on c.car_type=p.car_type and c.duration_type=p.duration_type
order by 2 desc, 1 desc

---

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

프로그래머스

USER_INFO 테이블

ONLINE_SALE 테이블

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

 

• 풀이(오답)

SELECT date_format(o.sales_date, '%Y') YEAR, 
        date_format(o.sales_date, '%m') MONTH,
        count(u.user_id) PURCHASED_USERS,
        round(count(u.user_id)/(SELECT count(*) FROM user_info WHERE joined like '2021%'), 1) PUCHASED_RATIO
FROM user_info u
JOIN online_sale o
ON u.user_id = o.user_id
WHERE u.joined LIKE '2021%'
GROUP BY date_format(o.sales_date, '%Y'), date_format(o.sales_date, '%m')

 

• 풀이(정답)

SELECT date_format(o.sales_date, '%Y') YEAR, 
        date_format(o.sales_date, '%m') MONTH,
        count(distinct u.user_id) PURCHASED_USERS,
        round(count(distinct u.user_id)/(SELECT count(*) FROM user_info WHERE joined like '2021%'), 1) PUCHASED_RATIO
FROM user_info u
JOIN online_sale o
ON u.user_id = o.user_id
WHERE u.joined LIKE '2021%'
GROUP BY date_format(o.sales_date, '%Y'), date_format(o.sales_date, '%m')

user_id에서 중복제거를 해줬어야 함 :(