[SQL/solvesql] LV3. 쇼핑몰의 일일 매출액과 ARPPU

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

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

 

이번 포스팅에서는 브라질 Olist 이커머스 데이터를 활용하여 2018년 1월 1일 이후의 일별 매출 데이터를 SQL로 분석하는 방법을 소개합니다. Olist 데이터는 olist_orders_dataset과 olist_order_payments_dataset 두 테이블로 구성되어 있습니다. 이 두 테이블을 조인하여 일별 결제 고객 수, 매출액, 그리고 ARPPU(Average Revenue Per Paying User)를 계산해 보겠습니다.

 

1. 분석 목표

분석 결과는 다음과 같은 네 가지 컬럼을 포함해야 합니다:

1. dt: 매출 날짜 (2018-01-01 형식)
2. pu: 결제 고객 수 (해당 날짜의 고유 고객 수)
3. revenue_daily: 해당 날짜의 총 매출액
4. arppu: 결제 고객 1인당 평균 결제 금액
   (ARPPU = 총 매출액 ÷ 결제 고객 수)

결과는 매출 날짜 기준으로 오름차순 정렬되어야 하며, 매출액과 ARPPU는 소수점 둘째 자리까지 반올림합니다.

 

2. 쿼리 작성

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

 

3. 쿼리 설명

1️⃣ DATE() 함수

• DATE(od.order_purchase_timestamp):
  • 주문 시각(order_purchase_timestamp)에서 날짜만 추출하여 dt 컬럼으로 사용합니다.
  • 시간 정보는 제거되고, 날짜 형식(YYYY-MM-DD)만 남게 됩니다.

2️⃣ 결제 고객 수(pu) 계산

• COUNT(DISTINCT od.customer_id):
  • 각 날짜별로 결제에 참여한 고유 고객 ID의 수를 계산합니다.

3️⃣ 매출액(revenue_daily) 계산

• SUM(pd.payment_value):
  • 결제 금액(payment_value)의 합계를 계산하여 해당 날짜의 총 매출액을 구합니다.

4️⃣ ARPPU(Average Revenue Per Paying User) 계산

• 공식: ARPPU = 총 매출액 ÷ 결제 고객 수
• SQL에서 ROUND(SUM(pd.payment_value) / COUNT(DISTINCT od.customer_id), 2)를 사용하여 소수점 둘째 자리까지 반올림합니다.

5️⃣ 조건 필터링

• WHERE DATE(od.order_purchase_timestamp) >= '2018-01-01':
  • 2018년 1월 1일 이후의 데이터만 분석 대상으로 설정합니다.

6️⃣ 데이터 그룹화

• GROUP BY DATE(od.order_purchase_timestamp):
  • 날짜별로 데이터를 그룹화하여 집계합니다.

7️⃣ 정렬

• ORDER BY dt:
  • 결과를 매출 날짜 기준으로 오름차순 정렬합니다.

 

이번 포스팅에서는 브라질 Olist 이커머스 데이터셋을 활용하여 SQL로 일별 매출 데이터와 ARPPU를 계산하는 방법을 알아보았습니다. SQL의 GROUP BY, JOIN, SUM, COUNT DISTINCT, ROUND와 같은 집계 함수와 조건문을 적절히 활용하면, 데이터를 효율적으로 집계하고 분석할 수 있습니다.