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

select round(avg(avg1),1) as avg_amount ,round(avg(sum2-avg1),1) as avg_cost from (
    select order_id,avg(total_amount)avg1,sum(price*cnt)sum2
    from 
        (select order_id,uid,total_amount,event_time,rank() over(partition by uid order by event_time) as ranking from tb_order_overall)t join tb_order_detail using(order_id)
        join tb_product_info using(product_id)
        where date_format(event_time,'%Y%m')='202110' and ranking=1
            and uid not in (select uid from tb_order_overall where date_format(event_time,'%Y%m')<'202110' )
        group by order_id)t1

思路就是算出10月每个人的首单，然后利用筛选去掉在10月前活跃过的用户。

然后计算他们的总额和优惠额即可。