[SQLite] solvesql 문제풀이 : COUNT, JOIN, 쇼핑몰의 일일 매출액과 ARPPU(1인 평균 결제금액)

https://solvesql.com/

쇼핑몰의 일일 매출액과 ARPPU https://solvesql.com/problems/daily-arppu/

solvesql

 

 

 

ARPPU

Average Revenue Per Paying User

결제 고객 1인 당 평균 결제 금액

= 전체 매출액 / 결제 고객 수

 

 

 

 

조건1) 2018년 1월 1일 이후 일별 집계
조건2) 결제 고객 수, 매출액, ARPPU(전체매출액/결제고객수)
조건3) 매출액과 ARPPU는 반올림 해 소수점 둘째자리까지

 

 

 

 

데이터를 천천히 뜯어보자.

 

 

 

2018년 이후의 결제건 확인하기.

SELECT orders.order_purchase_timestamp AS '구매시각'
      ,pays.payment_value AS '결제금액'
FROM olist_orders_dataset orders
      INNER JOIN olist_order_payments_dataset pays
      ON orders.order_id = pays.order_id
WHERE date(orders.order_purchase_timestamp) >= '2018-01-01'
ORDER BY orders.order_purchase_timestamp
LIMIT 10

 

 

 

데이터를 보다가 하루에 한명의 고객이 여러번 결제(또는 주문)한 케이스는 없나? 하는 호기심이 생김.

 

 

1. 한명의 고객이 하루에 여러번 결제를 하기도 하는가?

  payment_sequential 컬럼(연속 결제 횟수)  ≠ 1 인 케이스를 뽑아보니 제법 있었다.

  답) YES. 한 고객이 주문 한건을 결제할때 여러번에 걸쳐서 하기도 함

 

2. 한명의 고객이 하루에 여러번 주문을 하기도 하는가?

  customer_id 수  ≠  order_id 수 인 케이스가 없음 (아래 코드)

  답) NO. 한 고객은 하루에 최대 한건만 주문했다.

 

SELECT DATE(orders.order_purchase_timestamp) AS dt --매출날짜
      ,COUNT(distinct orders.customer_id) AS pu --결제고객수
      ,COUNT(distinct pays.order_id) AS order_count --주문건수
FROM olist_orders_dataset orders
      INNER JOIN olist_order_payments_dataset pays
      ON orders.order_id = pays.order_id
WHERE dt >= '2018-01-01'
GROUP BY dt
HAVING pu != order_count
ORDER BY dt

* distinct 주의

 

 

 

 

이제 문제가 요구하는 결제금액의 합과 결제고객 수를 일별로 산출해보자.

 

 

SELECT DATE(orders.order_purchase_timestamp) AS dt --매출날짜
      ,COUNT(distinct orders.customer_id) AS pu --결제고객수
      ,ROUND(SUM(pays.payment_value),2) AS revenue_daily --총 결제금액
      ,ROUND(SUM(pays.payment_value)/COUNT(distinct orders.customer_id),2) AS arppu
FROM olist_orders_dataset orders
      INNER JOIN olist_order_payments_dataset pays
      ON orders.order_id = pays.order_id
      -- 결제된 주문건에 한해서 주문정보/결제정보 테이블 합침
WHERE dt >= '2018-01-01'
GROUP BY dt
ORDER BY dt

 

distinct 를 넣어주지 않아서 처음에 ARPPU 값이 오답으로 나왔다.

더 세심히 관찰하고 생각하자.