쇼핑몰의 일일 매출액과 ARPPU

문제

✔️ 난이도 3

문제 흐름

두 테이블을 이용해 2018년 1월 1일 이후 일별로 집계된 쇼핑몰의 결제 고객 수, 매출액, ARPPU를 계산하는 쿼리를 작성해주세요.

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

➡️ 조건, 집계 내용
메인이 되는 데이터는 결제 내용이 되기 때문에 olist_order_payments_dataset를 베이스 테이블로 잡은 뒤, 결제 시각 등의 정보를 위해 LEFT JOIN을 통해 olist_orders_dataset를 합쳐준다.

구매 시각 order_purchase_timestamp이 2018-01-01 이후인 경우를 필터링하고,
집계는 "일별"이기 때문에 구매 시각을 date형으로 변환하여 그룹화의 기준으로 삼는다.

• 결제 고객 수: customer_id를 중복을 제거하여 count
• 매출액: 결제 금액 payment_value를 sum
• ARPPU: 매출액 / 결제 고객 수

매출액과 ARPPU는 반올림 해 소수점 둘째자리까지 출력해주세요.

➡️ 데이터 포맷
매출액과 ARPPU에 round 함수를 통해 소숫점 둘째자리까지만 출력하도록 한다.

쿼리 결과는 아래 네 개의 컬럼을 포함해야 하고, 매출 날짜 기준으로 오름차순 정렬되어 있어야 합니다.

➡️ 정렬
ORDER BY에 매출 날짜 (1번 컬럼)을 기준으로 오름차순 정렬하도록 명시한다.

코드

SELECT
  date(order_purchase_timestamp) dt,
  count(DISTINCT customer_id) pu,
  round(sum(payment_value), 2) revenue_daily,
  round(sum(payment_value) / count(DISTINCT customer_id), 2) arppu
FROM olist_order_payments_dataset p
LEFT JOIN olist_orders_dataset o
ON p.order_id = o.order_id
WHERE date(order_purchase_timestamp) >= '2018-01-01'
GROUP BY 1
ORDER BY 1;