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

select
  round(avg(total_amount),1),
  round(avg(cost),1)
from
(
    select
      order_id,
      uid,
      total_amount,
      event_time
    from
(
        select
          order_id,
          uid,
          total_amount,
          event_time,
          row_number() over (
            partition by uid
            order by
              event_time
          ) as rn
        from
          tb_order_overall

      ) a
    where
      rn = 1 
    and date_format(event_time, '%Y-%m') = '2021-10'
  ) aa
  left join(
    select
      a.order_id,
      sum(price * cnt) - avg(total_amount) as cost
    from
      tb_order_detail a
      left join (
        select
          order_id,
          uid,
          total_amount,
          event_time
        from
(
            select
              order_id,
              uid,
              total_amount,
              event_time,
              row_number() over (
                partition by uid
                order by
                  event_time
              ) as rn
            from
              tb_order_overall
          ) a
        where
          rn = 1
    and date_format(event_time, '%Y-%m') = '2021-10'
      ) b on a.order_id = b.order_id
    group by
      a.order_id
  ) bb on aa.order_id = bb.order_id