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

select
round(total_amount/num, 1) avg_amount
,round((total - total_amount)/num, 1) avg_cost
from
(
    select
    count(distinct uid) num 
    ,sum(price*cnt) total
    ,sum(distinct total_amount) total_amount
    from tb_order_detail d 
    join tb_order_overall o on d.order_id = o.order_id
    join tb_product_info i on d.product_id = i.product_id
    where (uid, event_time) in (
                                  select
                                  uid
                                  ,min(event_time)
                                  from tb_order_overall
                                  group by uid
                               ) #下单时间为最小下单时间即为新用户
    and date_format(event_time, '%Y-%m') = '2021-10' and status = 1
) tmp