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

❗ 본 포스팅은 Solvesql의  연습문제를 활용합니다. 다양한 SQL 문제를 풀어보고 싶다면 Solvesql를 방문하세요.

 

아래의 문제는쇼핑몰의 일일 매출액과 ARPPU 문제이다.

 

문제를 해석해보자.

1. 2018년 1월 1일이후의 주문을 기점으로 파악해야한다.
2. 일별로 결제 고객수, 매출액, ARPPU를 구한다.
3. 매출일 기준으로 오름차순 정렬한다.
4. 매출액과 ARPPU는 소수점 둘째자리까지 반올림한다.

 

테이블의 정의가 위처럼 되어있다.

 

 

select
  order_purchase_timestamp as dt,
  count(distinct (customer_id)) as pu,
  round(sum(payment_value),2) as revenue_daily,
  round(sum(payment_value) / count(distinct (customer_id)),2) as arppu
from
  (
    select
      order_id,
      customer_id,
      strftime('%Y-%m-%d', order_purchase_timestamp, 'localtime') as order_purchase_timestamp
    from
      olist_orders_dataset
    where
      order_purchase_timestamp >= '2018-01-01'
  ) as t1
  join (
    select
      *
    from
      olist_order_payments_dataset
  ) as t2 on (t1.order_id = t2.order_id)
group by
  order_purchase_timestamp
order by
  dt;

 

이전 문제와 다르게 이번에는 처리할 것들이 있다.

먼저 olist_orders_dataset 에서 order_purchase_timestamp를 ‘YYYY-MM-DD’형식으로 바꿔줘야 일별 집계가 가능하므로 SQLite에서 활용가능한 strftime 함수를 써서 ‘YYYY-MM-DD’형식으로 바꿔주고 2018년 1월 1일 이후인 것들을 가져와서 t1이라는 테이블로 만든다. 그리고 olist_order_payments_dataset 테이블을 가져와서 t2로 만들고 t1과 t2를 join해준다.

mysql과 다른 함수라서 찾는데 조금 애먹었다. 밑에 글을 참고하면 좋을듯하다.

 

https://javaexpert.tistory.com/180

MySQL과 SQLite 날짜 함수 비교 MySQL

 

 

이렇게 하면 하나의 테이블에 우리가 원하는 정보가 다 들어가게 된다. 우리는 일별로 집계를 해야하므로 우리가 ‘YYYY-MM-DD’형식으로 바꿨던 order_purchase_timestamp를 기준으로 group by 해준다. group by를 해서 select에는 집계함수가 들어가야한다.(count, sum 등)

select부분은 customer_id, 해당일자 매출액, 해당일자 arppu를 구하면 된다.

 

 

** arppu는 1인당 결제금액을 나타내므로 “전체 매출액/고객수” 하면 나온다.

** customer_id의 경우 유니크한 customer_id를 추출해야하므로 중복제거를 해준다.(distinct)