쇼핑몰의 일일 매출액과 ARPPU : solvesql

ARPPU(Average Revenue Per Paying User) : 유료 사용자 1인당 평균 수익

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

주문 ID (order_id)로 두 테이블 INNER JOIN

SELECT
  DATE(ood.order_purchase_timestamp) as dt,
  COUNT(DISTINCT ood.customer_id) as pu,
  ROUND(SUM(oopd.payment_value), 2) as revenue_daily,
  ROUND(SUM(oopd.payment_value) / COUNT(DISTINCT ood.customer_id), 2) as arppu 
FROM
  olist_orders_dataset AS ood
  INNER JOIN olist_order_payments_dataset as oopd ON ood.order_id = oopd.order_id
WHERE
  ood.order_purchase_timestamp >= '2018-01-01'
GROUP BY
  dt