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

with t0 as(
    select
    uid,
    order_id,
    sum(price*cnt)-avg(total_amount) as discont
    from tb_order_detail left join tb_order_overall using(order_id)
    where date_format(event_time,'%Y-%m')='2021-10'
    group by uid,order_id
),
t1 as(
    select
    uid,
    total_amount,
    # price*cnt-total_amount as discont,
    discont,
    ROW_NUMBER() over(partition by uid order by event_time asc) as tmp
    from tb_order_detail inner join tb_order_overall using(order_id)
    inner join tb_product_info using(product_id)
    inner join t0 using(uid,order_id)
    where date_format(event_time,'%Y-%m')='2021-10' and uid not in(
        select uid from tb_order_overall 
        where date_format(event_time,'%Y-%m')<'2021-10'
        )
    # group by uid
    # having tmp=1
)
select
round(sum(total_amount)/count(1),1) as avg_amount,
round(sum(discont)/count(1),1) as avg_cost
from t1
where tmp=1