[SolveSQL] 쇼핑몰의 일일 매출액, 쇼핑몰의 일일 매출액과 ARPPU

1. 쇼핑몰의 일일 매출액- https://solvesql.com/problems/olist-daily-revenue/

solvesql

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

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

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

• dt - 매출 날짜 (예: 2018-01-01)
• revenue_daily - 해당 날짜의 매출액

A. 아 근데 진짜 매번 '%y-%m-%d' 처럼 형식 입력하는 게 너무 귀찮다...자동완성 있었으면 ㅎ....

SELECT STRFTIME('%Y-%m-%d', OD.order_purchase_timestamp) AS "dt", ROUND(SUM(PD.payment_value),2) AS revenue_daily
FROM olist_orders_dataset OD
INNER JOIN olist_order_payments_dataset PD ON OD.Order_ID = PD.Order_ID
WHERE STRFTIME('%Y%m%d', OD.order_purchase_timestamp) >= '20180101'
GROUP BY STRFTIME('%Y%m%d', OD.order_purchase_timestamp)
ORDER BY "dt"

쿼리 결과가 3페이지나 나오니까 기분 좋은 통과 페이지로 인증 :-D

 

2. 쇼핑몰의 일일 매출액과 ARPPU - https://solvesql.com/problems/daily-arppu/

solvesql

위 문제랑 이어지는(?)데 이제 별개인 그런 문항이다...

Q. 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인 당 평균 결제 금액

일단 ARPPU = (전체 매출액) / (결제 고객 수) 라고 수식화해놓고 답을 내보았다. 단, 주의할 점은 계산 후 '1일 단위'로 변경해야 한다는 것,,,

SELECT DATE(orders.order_purchase_timestamp) AS dt 
      ,COUNT(distinct orders.customer_id) AS pu 
      ,ROUND(SUM(pays.payment_value),2) AS revenue_daily 
      ,ROUND(SUM(pays.payment_value)/COUNT(distinct orders.customer_id),2) AS arppu
FROM olist_orders_dataset orders
      INNER JOIN olist_order_payments_dataset pays
      ON orders.order_id = pays.order_id

WHERE dt >= '2018-01-01'
GROUP BY dt

후 끝! 역시나 무사히 도출됐는데 이 문제도 쿼리 결과가 3페이지나 나온다 :0

이제 solvesql도 1개만 더 풀면 끝인데 어려움이다! 뿌셔버리게쒀~~~