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

这个题还是容易。

#题目描述看起来不需要考虑退单的情况

select
    format(avg(d.payment),1) as avg_amount,
    format(avg(d.realPrice-d.payment),1) as avg_cost
from
(
    select
        b.order_id,max(total_amount) as payment,sum(c.price*c.cnt) as realPrice
    from 
    (
        select
        a.order_id,a.uid,date_format(a.event_time,'%Y-%m-%d') as time,a.total_amount,row_number() over(partition by a.uid order by a.event_time) as rk
        from tb_order_overall a
    ) b join tb_order_detail c on b.rk = 1 and b.time >= '2021-10-01' and b.time <= '2021-10-31' and b.order_id = c.order_id
    group by b.order_id
) d