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

select 
    round(avg(total_amount),1)
    ,round(avg(mny-total_amount),1)
from (
    select 
        uid
        ,event_time
        ,total_amount
        ,price
        ,cnt
    # 不能用rank，一个订单里多个商品的话，rk会重复，外查询不能做了
        ,row_number() over(partition by uid order by event_time) as rk
    # 对每个订单，多个商品求应付总价
        ,sum(price*cnt) over(partition by order_id) as mny  
    from tb_order_detail as od
    left join tb_order_overall as o using(order_id)
    where status = 1
    ) a
where rk = 1 and date_format(event_time,"%Y%m") = 202110