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

with b as ( --  为每个人的订单时间排序，取第一单编号
    select distinct order_id 
    from
    (select distinct row_number()over(partition by uid order by event_time) as rk,order_id
    from tb_order_overall )t1
    where rk=1

),
a as ( -- 根据b查询出的第一单编号，计算总支付价和原价（原价需要sum）
    select uid,a.total_amount,sum(b.price*b.cnt) as ori_price
    from 
    tb_order_overall a left join tb_order_detail b
    using(order_id) 
    left join tb_product_info c using(product_id)
    where date_format(a.event_time,'%Y-%m')='2021-10' and order_id in(select order_id from b)
    group by 1,2
)
 -- 根据总支付价和原价计算首单平均交易金额（客单价）和平均获客成本
select round(sum(a.total_amount)/count(distinct a.uid),1) as avg_amount, 
round((sum(ori_price)-sum(total_amount))/count(distinct a.uid),1) as avg_cost
from a