6.29
1) 연도별 배송 업체 이용 내역 분석하기
select year,
sum(Standard) as standard,
sum(Overnight) as overnight,
sum(Express) as express
from (
select year(purchased_at) as year,
(case when (shipping_method = 'Standard') and (is_returned is true) then 2
when (shipping_method = 'Standard') and (is_returned is not true) then 1
when (shipping_method != 'Standard') and (is_returned is true) then 1 else 0
end) as Standard,
case when shipping_method = 'Overnight' then 1 else 0 end as Overnight,
case when shipping_method = 'Express' then 1 else 0 end as Express
from transactions
) as t
group by 1
order by 1
2) A/B 테스트를 위한 버킷 나누기 2
with table1 as (
select distinct customer_id,
case when customer_id % 10 = 0 then 'A' else 'B' end as bucket
from transactions
), table2 as (
select customer_id,
count(distinct transaction_id) orders,
sum(total_price) revenue
from transactions
where is_returned = false
group by customer_id
)
select a.bucket,
count(a.customer_id) user_count,
round(avg(b.orders), 2) avg_orders,
round(avg(b.revenue), 2) avg_revenue
from table1 a
join table2 b on a.customer_id = b.customer_id
group by 1
6.30
1) 복수 국적 메달 수상한 선수 찾기
select name
from records as r
join athletes as a on r.athlete_id = a.id
join games as g on r.game_id = g.id
where medal is not null
and year >= 2000
group by a.id
having count(distinct team_id) >= 2
order by name
2) 배송 예정일 예측 성공과 실패
select date(order_purchase_timestamp) as purchase_date,
count(case when date(order_delivered_customer_date) < date(order_estimated_delivery_date) then order_id end) as success,
count(case when date(order_delivered_customer_date) >= date(order_estimated_delivery_date) then order_id end) as fail
from olist_orders_dataset
where order_delivered_customer_date is not null
and order_estimated_delivery_date is not null
and date(order_purchase_timestamp) between '2017-01-01' and '2017-01-31'
group by date(order_purchase_timestamp)
order by date(order_purchase_timestamp)
7.1
1) 지역별 주문의 특징
select Region,
count(distinct case when category = 'Furniture' then order_id else null end) Furniture,
count(distinct case when category = 'Office Supplies' then order_id else null end) 'Office Supplies',
count(distinct case when category = 'Technology' then order_id else null end) Technology
from records
group by 1
order by 1
7.2
1) 할부는 몇 개월로 해드릴까요
select payment_installments,
count(distinct order_id) as order_count,
min(payment_value) as min_value,
max(payment_value) as max_value,
avg(payment_value) as avg_value
from olist_order_payments_dataset
where payment_type = 'credit_card'
group by 1
2) 작품이 없는 작가 찾기
select a.artist_id,
a.name
from artists a
left join artworks_artists aw using(artist_id)
where a.death_year is not null
and aw.artwork_id is null
7.3
1) 멘토링 짝꿍 리스트
select e1.employee_id as mentee_id,
e1.name as mentee_name,
e2.employee_id as mentor_id,
e2.name as mentor_name
from employees e1
cross join employees e2
where e1.join_date between '2021-09-01' and '2021-12-31'
and e2.join_date <= '2019-12-31'
and e1.department != e2.department
order by 1, 3
2) 쇼핑몰의 일일 매출액과 ARPPU
select date(ood.order_purchase_timestamp) as dt,
count(distinct customer_id) as pu,
round(sum(oopd.payment_value),2) as revenue_daily,
round(sum(oopd.payment_value) / count(distinct customer_id),2) as arppu
from olist_orders_dataset ood
join olist_order_payments_dataset oopd using(order_id)
where date(ood.order_purchase_timestamp) >= '2018-01-01'
group by 1
order by 1
