SQL 코테 준비 (8월 1주)(프로그래머스-2회차)

8.3

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

select p.member_name,
       r.review_text,
       r.review_date
from member_profile p 
join rest_review r using(member_id)
where r.member_id = (select member_id from rest_review group by 1 order by count(*) desc limit 1)
order by 3,2

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

select sales_date,
       product_id,
       user_id,
       sales_amount
from online_sale
where sales_date between '2022-03-01' and '2022-03-31'

union all

select sales_date,
       product_id,
       NULL as user_id,
       sales_amount
from offline_sale
where sales_date between '2022-03-01' and '2022-03-31'

order by 1,2,3

8.4

1) 특정 세대의 대장균 찾기

select e1.id 
from ecoli_data e1
join ecoli_data e2 on e1.parent_id = e2.id
join ecoli_data e3 on e2.parent_id = e3.id
where e3.parent_id is null
order by 1

2) 입양 시각 구하기(2)

with recursive cte (hour) as
(
    select 0
    union all
    select hour + 1
    from cte
    where hour < 23
)
select cte.hour, count(animal_outs.animal_id) as 'count'
from cte
left join animal_outs
on cte.hour = hour(animal_outs.datetime)
group by cte.hour;

8.5

1) FrontEnd 개발자 찾기

select distinct d.id,
       d.email,
       d.first_name,
       d.last_name
from developers d
left join skillcodes s on s.code = d.skill_code & s.code
where s.category = 'Front End'
order by 1

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

select history_id,
       case when datediff(end_date, start_date) + 1 >= 90 
            then floor(daily_fee * (1 - (
                select discount_rate 
                from car_rental_company_discount_plan 
                where car_type = '트럭' and duration_type = '90일 이상'
            ) / 100) * (datediff(end_date, start_date) + 1))
            when datediff(end_date, start_date) + 1 >= 30 
            then floor(daily_fee * (1 - (
                select discount_rate 
                from car_rental_company_discount_plan 
                where car_type = '트럭' and duration_type = '30일 이상') / 100
            ) * (datediff(end_date, start_date) + 1))
            when datediff(end_date, start_date) + 1 >= 7 
            then floor(daily_fee * (1 - (
                select discount_rate 
                from car_rental_company_discount_plan 
                where car_type = '트럭' and duration_type = '7일 이상') / 100
            )* (datediff(end_date, start_date) + 1))
            else daily_fee * (datediff(end_date, start_date) + 1)
       end as fee
from car_rental_company_rental_history h
join car_rental_company_car c using(car_id)
where c.car_type = '트럭'
order by 2 desc, 1 desc;

8.6

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

select c.car_id,
       c.car_type,
       round(c.daily_fee * 30 * (100-p.discount_rate)/100) as fee
from car_rental_company_car c
join car_rental_company_rental_history h on c.car_id = h.car_id
join car_rental_company_discount_plan p on c.car_type = p.car_type
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)
order by 3 desc, 2, 1 desc;

8.7

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

select year(o.sales_date) as year,
       month(o.sales_date) as month,
       count(distinct o.user_id) as purchased_users,
       round((count(distinct o.user_id) / (select count(*) from user_info where joined like '2021%')),1) as purchased_ratio
from user_info u
join online_sale o using (user_id)
where u.joined like '2021%'
group by 1,2
order by 1,2

• 오답 풀이

select year(o.sales_date) as year,
       month(o.sales_date) as month,
       count(distinct o.user_id) as purchased_users,
       round((count(distinct o.user_id) / count(distinct u.user_id)),2) as purchased_ratio
from user_info u
join online_sale o using (user_id)
where o.sales_date like '2021%'
and u.joined like '2021%'
group by 1,2;

=> 전체 가입자 대비가 아니라 해당 월 구매자 대비로 비율을 계산하게 되어서 정답이 될 수 없음.