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

select
  round(avg(total_amount), 1) as avg_amount,
  round(avg(cost_discount), 1) as avg_cost
from
  (
    select
      order_id,
      total_amount,
      (sum(price * cnt) - total_amount) as cost_discount
    from
      tb_order_overall
      join tb_order_detail 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
      )
    group by
      order_id,
      total_amount