SQL 코테 준비 (7월 3주)(solvesql)

7.13

1) 이달의 작가 후보 찾기

select author
from books
where genre = 'Fiction'
group by 1
having count(*) >= 2
and avg(user_rating) >= 4.5
and avg(reviews) >= (select avg(reviews) from books where genre = 'Fiction')
order by 1

2) 가구 판매의 비중이 높았던 날 찾기

select order_date,
       count(distinct case when category = 'Furniture' then order_id end) as furniture,
       round(count(distinct case when category = 'Furniture' then order_id end) / 
             count(distinct order_id) * 100,2) as furniture_pct
from records
group by 1
having count(distinct order_id) >= 10
and furniture_pct >= 40
order by 3 desc, 1

7.14

1) 펭귄 날개와 몸무게의 상관 계수

with base as (
    select species,
           flipper_length_mm,
           avg(flipper_length_mm) over(partition by species) avg_fl, 
           body_mass_g,
           avg(body_mass_g) over(partition by species) avg_bm
    from penguins
  )

select species,
       round(sum( (flipper_length_mm - avg_fl) * (body_mass_g - avg_bm))
             / sqrt(sum(power(flipper_length_mm - avg_fl, 2)))
             / sqrt(sum(power(body_mass_g - avg_bm, 2))), 3) as corr
from base
group by 1

- 피어슨 상관계수

7.15

1) 유량(Flow)와 저량(Stock)

select year(acquisition_date) as 'Acquisition year',
       count(distinct artwork_id) as 'New acquisitions this year (Flow)',
       sum(count(distinct artwork_id)) over(order by year(acquisition_date))'Total collection size (Stock)'
from artworks
where year(acquisition_date) is not null
group by 1
having count(distinct artwork_id) > 0
order by 1

7.16

1) 세 명이 서로 친구인 관계 찾기

select e1.user_a_id,
       e2.user_a_id as user_b_id,
       e2.user_b_id as user_c_id
from edges e1
join edges e2 on e1.user_b_id = e2.user_a_id
join edges e3 on e2.user_b_id = e3.user_b_id and e1.user_a_id = e3.user_a_id
where e1.user_a_id < e2.user_a_id < e2.user_b_id
and (e1.user_a_id = 3820 or e2.user_a_id = 3820 or e2.user_b_id = 3820)

7.17

1) 신규 유입을 견인하는 카테고리

select r.category,
       r.sub_category,
       count(distinct r.order_id) as cnt_orders
from records r
join customer_stats c using(customer_id)
where r.order_date = c.first_order_date
group by 1,2
order by 3 desc