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

select round(sum(total)/count(distinct uid),1) as avg_amount
,round((sum(act)-sum(total))/count(distinct uid),1) as avg_cost
from(# 条件过滤：2021-10新增用户及该用户2021-10期间订单的首单
    select 
    uid,avg(total_amount) as total,sum(price*cnt) as act
    from (#表关联，求用户首次日期和付款订单过滤
        select uid
        ,date(event_time) as dt 
        ,min(date(event_time)) over(partition by uid) as first_dt
        ,dense_rank() over(partition by uid order by event_time ) as rn  #首单标签
        ,total_amount
        ,price,cnt
        from tb_order_overall t1
        join tb_order_detail t2 on t1.order_id=t2.order_id
        where t1.status=1
    ) t
    where date_format(first_dt,'%Y-%m')='2021-10' 
    and date_format(dt,'%Y-%m')='2021-10'
    and rn =1
    group by uid
) tt