[SQL문제풀기] 쇼핑몰의 일일 매출액과 ARPPU

문제

solvesql.com

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

SQLite

내가 작성한 오답

select strftime('%Y-%m-%d',order_purchase_timestamp) dt, count(distinct p.order_id) pu,
      round(sum(p.payment_value),2) revenue_daily, round(avg(p.payment_value),2) arppu
from olist_orders_dataset o
join olist_order_payments_dataset p
on o.order_id = p.order_id and order_purchase_timestamp >= '2018-01-01'
group by dt
order by 1;

내가 작성한 정답

select strftime('%Y-%m-%d',order_purchase_timestamp) dt, 
      count(distinct p.order_id) pu,
      round(sum(p.payment_value),2) revenue_daily,
       round(sum(p.payment_value)/count(distinct o.customer_id),2) arppu
from olist_orders_dataset o
join olist_order_payments_dataset p
on o.order_id = p.order_id and order_purchase_timestamp >= '2018-01-01'
group by dt
order by 1;