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

1.将总表按照用户分组，时间升序，使用窗口函数，取最小时间 row_number() over(partition by uid order by event_time) rk

2.将两表联查,最小的时间为10月份的 即是10月新用户 1）rk=1

2. status=1
3. event_time=2021-10

    select a.uid,a.order_id,a.total_amount amount,sum(b.price * b.cnt)-a.total_amount kcb
    from (        
        select *,row_number() over(partition by uid order by event_time ) rk from tb_order_overall           
    ) a  
    join tb_order_detail b
    using(order_id)
    where a.rk=1 and a.status=1 and date_format(a.event_time, '%Y-%m') = '2021-10'
    group by a.uid,a.order_id,amount
)
select round(avg(amount),1),round(avg(kcb),1) from t