7.6
1) 온라인 쇼핑몰의 월 별 매출액 집계
select substr(order_date, 1, 7) as order_month,
sum(case when oi.order_id not like 'C%' then oi.price * oi.quantity else 0 end) ordered_amount,
sum(case when oi.order_id like 'C%' then oi.price * oi.quantity else 0 end) canceled_amount,
sum(oi.price * oi.quantity) as total_amount
from order_items oi
join orders o using(order_id)
group by 1
order by 1
2) 서울숲 요일별 대기오염도 계산하기
select weekday, no2, o3, co, so2, pm10, pm2_5
from (
select case when dayofweek(measured_at) = 1 then '일요일'
when dayofweek(measured_at) = 2 then '월요일'
when dayofweek(measured_at) = 3 then '화요일'
when dayofweek(measured_at) = 4 then '수요일'
when dayofweek(measured_at) = 5 then '목요일'
when dayofweek(measured_at) = 6 then '금요일'
when dayofweek(measured_at) = 7 then '토요일'
end as weekday,
round(avg(no2),4) as no2,
round(avg(o3),4) as o3,
round(avg(co),4) as co,
round(avg(so2),4) as so2,
round(avg(pm10),4) as pm10,
round(avg(pm2_5),4) as pm2_5,
case when dayofweek(measured_at) = 1 then 7
else dayofweek(measured_at) - 1 end as week_num
from measurements
group by 1,8
order by 8
) as t
7.7
1) 게임 평점 예측하기 1
with base as (
select genre_id,
round(avg(critic_score),3) as avg_critic_score,
ceil(avg(critic_count)) as avg_critic_count,
round(avg(user_score),3) as avg_user_score,
ceil(avg(user_count)) as avg_user_count
from games
group by 1
)
select game_id,
name,
ifnull(g1.critic_score, g2.avg_critic_score) as critic_score,
ifnull(g1.critic_count, g2.avg_critic_count) as critic_count,
ifnull(g1.user_score, g2.avg_user_score) as user_score,
ifnull(g1.user_count, g2.avg_user_count) as user_count
from games g1
join base g2 on g1.genre_id = g2.genre_id
where year >= 2015
and (g1.critic_score is null or g1.user_score is null)
2) 올림픽 메달이 있는 배구 선수
select distinct a.id,
a.name,
r.medal as medals
from athletes a
join records r on a.id = r.athlete_id
join teams t on r.team_id = t.id
join events e on e.id = r.event_id
where t.team = 'KOR'
and e.event = 'Volleyball Women''s Volleyball'
and r.medal is not null
7.8
1) 크리스마스를 기념할 완벽한 와인 찾기 🥂
select *
from wines
where color = 'white'
and quality >= 7
and density > (select avg(density) from wines)
and residual_sugar > (select avg(residual_sugar) from wines)
and pH < (select avg(pH) from wines where color = 'white')
and citric_acid > (select avg(citric_acid) from wines where color = 'white')
2) 전국 카페 주소 데이터 정제하기
with base as (
select cafe_id,
substr(address, 1, instr(address, ' ') - 1) sido,
substr(address, instr(address, ' ') + 1, length(address)) exclude_sido
from cafes
)
select sido,
substr(exclude_sido, 1, instr(exclude_sido, ' ') - 1) sigungu,
count(distinct cafe_id) cnt
from base
group by 1, 2
order by 3 desc
7.9
1) 장르, 연도별 게임 평론가 점수 구하기
select ge.name as genre,
round(avg(case when year = 2011 then ga.critic_score end),2) as score_2011,
round(avg(case when year = 2012 then ga.critic_score end),2) as score_2012,
round(avg(case when year = 2013 then ga.critic_score end),2) as score_2013,
round(avg(case when year = 2014 then ga.critic_score end),2) as score_2014,
round(avg(case when year = 2015 then ga.critic_score end),2) as score_2015
from games ga
join genres ge using(genre_id)
group by 1
2) 초기 사용자의 친구 관계 찾기
with id_rank as (
select user_a_id,
user_b_id,
user_a_id + user_b_id as id_sum,
rank() over (order by user_a_id + user_b_id) as id_sum_rank
from edges
)
select user_a_id,
user_b_id,
id_sum
from id_rank
where id_sum_rank / (select count(*) from edges) <= 0.001
7.10
1) 레스토랑 요일 별 구매금액 Top 3 영수증
select day,
time,
sex,
total_bill
from (
select *,
dense_rank() over(partition by day order by total_bill desc) as rk
from tips
) as t
where rk <= 3
