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

with 
t0 as(
    select distinct uid
    from tb_order_overall
    where date_format(event_time,'%Y%m')='202110'),
t1 as(
    select distinct uid
    from tb_order_overall
    where date_format(event_time,'%Y%m')<'202110'),
t2 as(
    select distinct uid
    from t0 where uid not in(select uid from t1)),
t3 as(
    select
        b.uid,
        b.order_id,
        a.product_id,
        date(b.event_time) as sale_date,
        b.total_amount,
        a.price,
        a.cnt,
        dense_rank() over(partition by b.uid order by date(b.event_time)) as dt_rn
    from tb_order_detail a 
    left join tb_order_overall b on a.order_id=b.order_id
    left join tb_product_info c on a.product_id=c.product_id
    where 
        date_format(event_time,'%Y%m')='202110'
        and b.status=1
        and uid in(select uid from t2)),
t4 as(
    select
        distinct uid,
        order_id,
        total_amount
    from t3 where dt_rn=1),
t5 as(
    select
        uid,order_id,
        sum(price*cnt) as total_price
    from t3 
    where dt_rn=1
    group by uid,order_id)

select
    round(avg(t4.total_amount),1) as avg_amount,
    round(avg(t5.total_price-t4.total_amount),1) as avg_cost
from t4 left join t5 using(uid,order_id)