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

with a as (
select a1.uid, a1.order_id, a1.total_amount as actual_paid, rank() over(partition by uid order by event_time asc) as rk,date_format(event_time,'%Y-%m') as event_month from tb_order_overall a1
where a1.status = 1
),
b as (select order_id,sum(price*cnt) as order_amount from tb_order_detail group by 1),
c as (select a.uid, (order_amount-actual_paid) as cost, actual_paid from a join b 
on a.order_id = b.order_id
and a.rk = 1
and a.event_month = '2021-10' ) -- 题目里需要是10月份下首单的用户 用这两个条件筛选
select round(avg(actual_paid),1) as avg_amount, round(avg(cost),1) as avg_cost from c;

卡在第三个用例研究了半天发现是要10月份下单的新用户，也就是10月第一次下单的用户，10月是第一笔订单时间，10月前没下过单，改了query通过了