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

4、利用order_id连接两张表表 然后进行avg操作就行
select 
    distinct
    round(avg(total_amount) over(),1) `avg_amount`,
    round(avg(-t1.total_amount+t2.order_sum) over(),1) `avg_cost`
from( 
    #2、选取出10月份的新用户并排序
    select 
        order_id,
        uid,
        total_amount,
        row_number() over(partition by uid order by event_time ) rk
    from tb_order_overall
    where 
        uid not in (
		# 1、10月份之前活跃过的用户id
		select 
            uid
        from tb_order_overall
        where date_format(event_time,'%Y%m')<'202110'
        group by uid)
        and date_format(event_time,'%Y%m')='202110'
    ) t1
join 
(
    #3、获取每个订单的订单总额
    select
        order_id,
        sum(price*cnt) `order_sum`
    from tb_order_detail
    group by order_id
)t2 on t1.order_id=t2.order_id
where t1.rk=1