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

with reg
as(
    select
    uid
    ,min(event_time) first_time
    from tb_order_overall
    group by uid
)

select
round(avg(total_amount), 1) as avg_amount
,round(avg(cost), 1) as cost
from(

    select
    reg.uid as uid
    ,avg(total_amount) as total_amount
    ,sum(price * cnt) - avg(total_amount) as cost
    from tb_order_detail a 
    left join tb_order_overall b 
    on a.order_id = b.order_id
    left join reg
    on b.event_time = reg.first_time
    and b.uid = reg.uid
    where left(event_time, 7) = '2021-10' 
    and first_time is not null 
    group by 1
) t