쇼핑몰의 일일 매출액 & 쇼핑몰의 일일 매출액과 ARPPU (solvesql)

쇼핑몰의 일일 매출액

https://solvesql.com/problems/olist-daily-revenue/

SELECT
  DATE_FORMAT(order_purchase_timestamp, '%Y-%m-%d') dt,
  round(sum(payment_value), 2) revenue_daily
FROM
  olist_orders_dataset D
  JOIN olist_order_payments_dataset P ON D.order_id = P.order_id
WHERE
  order_purchase_timestamp >= '2018-01-01'
GROUP BY
  dt
ORDER BY
  dt

쇼핑몰의 일일 매출액과 ARPPU

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

DISTINCT를 꼭 주의 해야합니다!

SELECT
  DATE_FORMAT(order_purchase_timestamp, "%Y-%m-%d") AS dt,
  count(DISTINCT customer_id) pu,
  ROUND(SUM(payment_value), 2) revenue_daily,
  ROUND(SUM(payment_value) / COUNT(distinct customer_id), 2) AS arppu
FROM
  olist_orders_dataset D
  JOIN olist_order_payments_dataset P ON D.order_id = P.order_id
WHERE
  order_purchase_timestamp > "2018-01-01"
GROUP BY
  dt
ORDER BY
  dt