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

1.先把优惠前的订单总额算出来
select  order_id
        ,sum(price*cnt) ra
from tb_order_detail
group by order_id

2.再连接订单总表，用min(event_time)筛选出最早订单在10月份的用户
select  order_id
        ,uid
        ,total_amount
        ,ra
        ,date(event_time) dt
from tb_order_overall
join (select  order_id
        ,sum(price*cnt) ra
from tb_order_detail
group by order_id) a using(order_id)
where date_format(event_time,'%Y-%m')='2021-10' and (uid,event_time) in (select uid,min(event_time) from tb_order_overall group by uid)
3.计算

完整代码：
with t1 as(
select  order_id
        ,uid
        ,total_amount
        ,ra
        ,date(event_time) dt
from tb_order_overall
join (select  order_id
        ,sum(price*cnt) ra
from tb_order_detail
group by order_id) a using(order_id)
where date_format(event_time,'%Y-%m')='2021-10' and (uid,event_time) in (select uid,min(event_time) from tb_order_overall group by uid))

select  round(sum(total_amount)/count(uid),1) avg_amount
        ,round(sum(ra-total_amount)/count(uid),1) avg_cost
from t1