데이터 불러오기
문제 1 : 평균 배송 거리가 가장 긴 카테고리와 그 평균 거리를 구하시오.
- 각 주문의 배송 거리 = 고객 위치와 판매자 위치 간의 직선 거리로 계산
- 힌트: 위도와 경도 데이터를 활용하여 거리 계산 (Haversine Formula)
- 조건: 결과는 가장 배송 거리가 긴 제품 카테고리 1개를 반환.
with customer as (
select ocd.customer_id
, ocd.customer_zip_code_prefix
, ogd.geolocation_lat as customer_lat
, ogd.geolocation_lng as customer_lng
from olist_customers_dataset as ocd
left join olist_geolocation_dataset as ogd on ocd.customer_zip_code_prefix = ogd.geolocation_zip_code_prefix
), seller as (
select osd.seller_id
, osd.seller_zip_code_prefix
, ogd.geolocation_lat as seller_lat
, ogd.geolocation_lng as seller_lng
from olist_sellers_dataset as osd
left join olist_geolocation_dataset as ogd on osd.seller_zip_code_prefix = ogd.geolocation_zip_code_prefix
), t1 as (
select distinct ooid.order_id
, c.*
, s.*
from olist_order_items_dataset as ooid
inner join olist_orders_dataset as ood on ooid.order_id = ooid.order_id
inner join customer as c on c.customer_id = ood.customer_id
inner join seller as s on s.seller_id = ooid.seller_id
)
select order_id
, 6371 * ACOS(
COS(RADIANS(customer_lat)) * COS(RADIANS(seller_lat)) * COS(RADIANS(seller_lng) - RADIANS(customer_lng)) +
SIN(RADIANS(customer_lat)) * SIN(RADIANS(seller_lat))
) AS distance_km
from t1
limit 10
select customer_id, avg(customer_lat), avg(customer_lng)
from customer
group by customer_id
문제 2 : 주문 건수가 가장 많은 판매자 상위 3명을 구하고, 이들이 처리한 평균 배송 시간(영업일 기준)을 계산하시오.
- 배송 시간 = order_delivered_customer_date - order_approved_at
- 조건: 결과는 상위 3명의 판매자와 그들의 평균 배송 시간(일 단위).
with t1 as ( 필요한 데이터 선택
select ood.*
, ooid.seller_id
, ooid.shipping_limit_date
, datediff(ood.order_delivered_customer_date,ood.order_approved_at) as d_period
from olist_orders_dataset as ood
inner join olist_order_items_dataset as ooid on ood.order_id = ooid.order_id
), t2 as ( 판매자 상위 3명
select seller_id
, count(*) as selling
from t1
group by seller_id
order by selling desc
limit 3
)
-- 판매자 상위 3명의 평균 배송 시간 계산
select seller_id
, avg(d_period) as avg_d_period
from t1
where seller_id in (select seller_id from t2)
group by seller_id
문제 3 : 제품 리뷰 점수와 결제 유형별 상관관계를 분석하여 상관관계가 가장 높은 결제 유형을 구하시오.
- 상관관계는 Pearson 상관 계수로 계산.
- 조건: 리뷰 점수와 결제 금액(payment_value)의 상관관계를 계산.
select oord.review_score, oopd.payment_value
from olist_order_reviews_dataset as oord
inner join olist_order_payments_dataset as oopd on oord.order_id = oopd.order_id
문제 4 : 고객 지역별(우편번호 앞 3자리) 월별 총 매출을 계산하고, 가장 매출이 높은 지역과 월을 구하시오.
- 월별 총 매출 = 모든 결제 금액의 합
- 조건: 결과는 월별로 가장 매출이 높은 지역과 해당 매출 금액을 반환.
with t1 as (
select substr(ocd.customer_zip_code_prefix,3) as c_code
, ood.order_purchase_timestamp
, year(ood.order_purchase_timestamp) as y_date
, month(ood.order_purchase_timestamp) as m_date
, ooid.price
from olist_customers_dataset as ocd
inner join olist_orders_dataset as ood on ocd.customer_id = ood.customer_id
inner join olist_order_items_dataset as ooid on ooid.order_id = ood.order_id
)
select c_code
, y_date
, m_date
, sum(price) as total_sales
from t1
group by c_code, y_date, m_date
order by total_sales desc
limit 1
문제 5 : 구매자와 판매자의 지리적 분포를 분석하여 가장 일반적인 구매-판매 지역 쌍(우편번호 앞 3자리 기준)을 구하시오.
- 지역 쌍 = 구매자 지역과 판매자 지역
- 조건: 구매자와 판매자 간 거래가 가장 많은 지역 쌍 1개를 반환.
with t1 as (
select substr(ocd.customer_zip_code_prefix,3) as customer_code
, ooid.*
, substr(osd.seller_zip_code_prefix, 3) as seller_code
from olist_order_items_dataset as ooid
inner join olist_sellers_dataset as osd on ooid.seller_id = osd.seller_id
inner join olist_orders_dataset as ood on ood.order_id = ooid.order_id
inner join olist_customers_dataset as ocd on ocd.customer_id = ood.customer_id
)
select customer_code, seller_code, count(order_id) as cnt
from t1
group by customer_code, seller_code
order by cnt desc
limit 1
