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

根据题意，首先，先确定出2021年10月来的新增用户，以及首单的订单信息，作为子表

1. 首单，即每个用户的最小订单号
2. 新增用户，即发生的最早时间节点下的uid 可以表示为：

 select a.order_id
        , a.uid 
        , sum(price*cnt) as order_amount
    from 
       (
            select min(order_id) as order_id
            ,uid
            from tb_order_overall o 
            group by uid 
            having date_format(min(event_time),'%Y-%m') = '2021-10'
        )a

接着，将子表与订单金额等关键信息表左连接，找出uid，orderid，以及订单金额量

select a.order_id
        , a.uid 
        , sum(price*cnt) as order_amount
    from 
       (
            select min(order_id) as order_id
            ,uid
            from tb_order_overall o 
            group by uid 
            having date_format(min(event_time),'%Y-%m') = '2021-10'
        )a
        left join tb_order_detail t 
        on a.order_id = t.order_id
    group by 2

最后，现在所需要的各项基本信息已知，需要进一步展开计算，则有最终代码 ：

select  round(sum(or_amount)/count(distinct c.order_id),1) as avg_amount
      , round(sum(cost_amount)/count(distinct c.order_id),1)as avg_cost
from 
(
  select b.order_id
    , sum(b.order_amount- o.total_amount) as cost_amount
    , sum(o.total_amount) as or_amount
  from 
      (
      select a.order_id
          , a.uid 
          , sum(price*cnt) as order_amount
      from 
         (
              select min(order_id) as order_id
              ,uid
              from tb_order_overall o 
              group by uid 
              having date_format(min(event_time),'%Y-%m') = '2021-10'
          )a
      left join tb_order_detail t 
      on a.order_id = t.order_id
      group by 2
      )b
  left join tb_order_overall o 
  on b.order_id = o.order_id
  group by b.order_id
)c