[SQL풀이][solvesql][난이도 3] 쇼핑몰의 일일 매출액과 ARPPU

 

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

 

문제이해

• 이커머스 사이트의 일별 매출, 일별 결제고객수, 일별 결제고객당 평균 결제금액 (ARPPU)를 구하라
  • ARPPU : Average Revenue Per Paying Users (결제고객당 평균 결제금액)
    • 계산식 : ARPPU = daily revenue / # paying users
• 조건
  • 2018년 1월 1일 이후 내역만 조회
  • 날짜기준 오름차순 정렬
• 테이블
  • orders 
    • order_id, customer_id, order_purchase_timestamp(구매시각)
  • order_payments
    • order_id, payment_value

 

접근방법

• order_id를 기준으로 두 테이블(orders, order_payments) JOIN
• DATE 함수를 활용하여 날짜 정보만 획득 -> dt 컬럼 생성
• 위에서 생성한 dt컬럼을 기준으로 GROUP BY 실행
  • COUNT 고유 customer_id  -> pu 컬럼 생성
  • SUM payment_value -> revenue_daily 컬럼 생성
  • revenue_daily / pu 계산 -> arppu 컬럼 생성
• 2018년 1월 1일 이후 데이터 필터링
• dt 컬럼 기준 오름차순 정렬

 

풀이코드

SELECT
  DATE(o.order_purchase_timestamp) AS dt,
  COUNT(DISTINCT o.customer_id) AS pu, 
  SUM(op.payment_value) AS revenue_daily,
  ROUND( SUM(op.payment_value) / COUNT(DISTINCT o.customer_id), 2) AS arppu
FROM olist_orders_dataset AS o
LEFT JOIN olist_order_payments_dataset AS op USING (order_id)
WHERE dt >= '2018-01-01'
GROUP BY dt;