solvesql - 쇼핑몰의 일일 매출액과 ARPPU(Group by)

문제 출처 : https://solvesql.com/problems/daily-arppu/

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

 

 

 

1. 문제

난이도3, 정답률 37.97%

Brazilian E-Commerce Public Dataset by Olist 데이터셋은 브라질의 이커머스 웹사이트인 Olist Store의 판매 데이터 입니다. 그 중 olist_orders_dataset 테이블에는 주문 ID, 고객 ID, 주문 상태, 구매 시각 등 주문 내역 데이터가 들어있습니다. olist_order_payments_dataset 테이블에는 주문 ID, 결제 방법, 결제 금액 등 각 주문의 결제와 관련된 정보가 저장되어 있습니다. 두 테이블을 이용해 2018년 1월 1일 이후 일별로 집계된 쇼핑몰의 결제 고객 수, 매출액, ARPPU를 계산하는 쿼리를 작성해주세요.

ARPPU는 Average Revenue Per Paying User의 약자로, 결제 고객 1인 당 평균 결제 금액을 의미합니다. 전체 매출액을 결제 고객 수로 나누면 ARPPU를 계산할 수 있습니다.

주문 각각에 대해 매출이 일어나는 시점은 olist_orders_dataset 테이블의 order_purchase_timestamp 컬럼에 기록되고, 주문 금액은 olist_order_payments_dataset 테이블의 payment_value 컬럼에 기록됩니다.

쿼리 결과는 아래 네 개의 컬럼을 포함해야 하고, 매출 날짜 기준으로 오름차순 정렬되어 있어야 합니다. 매출액과 ARPPU는 반올림 해 소수점 둘째자리까지 출력해주세요.

-  dt - 매출 날짜 (예: 2018-01-01)
-  pu - 결제 고객 수
-  revenue_daily - 해당 날짜의 매출액
-  arppu - 결제 고객 1인 당 평균 결제 금액

 

2. 조건

• 2018-01-01이후 주문을 집계하여 결과를 출
• 매출액과 평균 결제 금액은 반올림하여 소수점 둘째자리까지 출력
• 매출 날짜별로 집계 필요

 

3. 쿼리

SELECT 
   DATE(ood.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 olist_orders_dataset AS ood
JOIN olist_order_payments_dataset AS oopd ON ood.order_id = oopd.order_id  
WHERE ood.order_purchase_timestamp >= '2018-01-01'
GROUP BY DATE(ood.order_purchase_timestamp)

매출 날짜별로 결과를 출력해야하기 때문에 Group By를 DATE(ood.order_purchase_timestamp)로 걸어 집계를 한다. 그리고 조건중에 2018년 1월 1일 이후의 데이터를 집계해야 하기 때문에 Where절에 날짜 조건을 걸어주고 날짜, 중복을 제외한 고객의 수, 매출액, 고객당 평균 매출액을 각각 구해서 출력해준다. 

 

4. 실행 결과