[solvesql]쇼핑몰의 일일 매출액과 ARPPU

solvesql 사이트의 쇼핑몰의 일일 매출액과 ARPPU 문제 정답과 풀이이다. 난이도는 3단계였고, 한번에 정답을 맞췄다. 풀이하면서 쿼리 수행시간을 최대한 낮추려고 신경쓰면서 풀었다.

 

• 난이도 : 3
• 풀이시간 : 15분
• 수행시간 : 0.998초

https://solvesql.com/problems/daily-arppu/

 

 

정답 쿼리

select date(order_purchase_timestamp) as dt, count(*) as pu, 
        round(sum(revenue),2) as revenue_daily, round(sum(revenue)/count(*),2) as arppu
from olist_orders_dataset a
left join 
      (select order_id, sum(payment_value) as revenue
      from olist_order_payments_dataset
      group by order_id) b
on a.order_id = b.order_id
where order_purchase_timestamp >= '2018-01-01'
group by 1;

 

 

해설

olist_orders_dataset 테이블의 order_id는 주문 하나하나의 id로, 유니크 값이다. 즉, order_id로 그룹화 시켜서 count(*)을 하면 count(distinct order_id)를 한 값과 똑같은 값이 나온다.

 

반면, olist_order_payments_dataset 테이블의 order_id는 유니크 값이 아니다. 하나의 주문 안에서 여러개의 상품을 구매할수 있기 때문이다. 그래서 order_id를 그룹화 시킨 후, sum(payment_value)를 하면 하나의 주문에서 총 얼마의 상품을 구매했는지가 계산된다. 이 부분이 left join의 서브쿼리에 들어가는 부분이다.

 

미리 payment_value를 집계한 후에 left join을 하게 되면 order_id가 1:1로 정확하게 매칭된다. 또한, 필요한 컬럼만 선택하여 데이터를 줄인 상태로 join을 하기 때문에 수행속도가 빨라진다.

 

나머지는 문제에 나와있는대로 계산만 해주면 되는 문제였다.