[solvesql] SQL 문제 풀이 (4)

1. 쇼핑몰의 일일 매출액과 ARPPU

select
  date(ood.order_purchase_timestamp) as dt,
  count(distinct (ood.customer_id)) as pu,
  round(sum(oopd.payment_value), 2) as revenue_daily,
  round(
    sum(oopd.payment_value) / count(distinct (ood.customer_id)),
    2
  ) as arppu
from
  olist_orders_dataset ood
  join olist_order_payments_dataset oopd on ood.order_id = oopd.order_id
where
  ood.order_purchase_timestamp >= '2018-01-01'
group by
  date(ood.order_purchase_timestamp)
order by
  date(ood.order_purchase_timestamp) asc;

 

2. 점검이 필요한 자전거 찾기

select
  bike_id
from
  rental_history
where
  date(rent_at) between '2021-01-01' and '2021-01-31'
group by
  bike_id
having
  sum(distance) >= 50000;