题解 | #10月的新户客单价和获客成本#

with t1 as (
    select order_id, sum(price*cnt) price_cnt
    from tb_order_detail
    group by order_id
    )

select round(sum(total_amount)/count(distinct too.order_id),1) avg_amount,
    round(sum(price_cnt-total_amount)/count(distinct too.order_id),1) avg_cost
from tb_order_overall too
inner join t1 using(order_id)
where date_format(event_time,'%Y-%m')='2021-10'
  and (uid, date(event_time)) in (
    select uid, min(date(event_time))
    from tb_order_overall
    group by uid
  )