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

select round(sum(total_amount)/count(order_id),1) avg_amount,
       round(avg(cost),1) avg_cost
from (select a.order_id, 
             total_amount,
             (sum(price*cnt) - total_amount) as cost
      from tb_order_detail a
      left join tb_order_overall b
      on a.order_id = b.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 )
      GROUP BY a.order_id) a