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

with xinyonghu as(
  select
    distinct order_id xyh_order_id
  from
(
      select
        *,
        min(event_time) over (partition by uid) zuizao_time
      from
        tb_order_overall
    ) temp
  where
    zuizao_time between '2021-10-01 00:00:00' and '2021-10-31 23:59:59'
),shoudan as(
    select *
    from(
    select
    *,ROW_NUMBER() over (partition by uid order by event_time) xiadan_rank
    from
    (
        select *
        from tb_order_overall
        where event_time between '2021-10-01 00:00:00' and '2021-10-31 23:59:59'
    ) temp1)temp2
where xiadan_rank=1
),dingdan as(
    select order_id,sum(price*cnt) zongjine
    from tb_order_detail
    group by order_id
) select round(avg(total_amount),1),
round(avg(zongjine-total_amount),1)
from  shoudan 
left join xinyonghu on shoudan.order_id=xinyonghu.xyh_order_id
left join dingdan on shoudan.order_id = dingdan.order_id
where xinyonghu.xyh_order_id is not null

设置三个全局表

1、筛选出题目条件下的新用户的表 t1

2、筛选出当月首单表（不区分是否新用户）t2

3、订单原价表（为了后面算出优惠金额）t3

计算题目指标

1、合并

以首单表作为主表（即以订单为最小粒度），以订单号左拼接表1,（后面只保留 is not null 的行）

再拼接订单原价表，以订单号作为连接值

2、计算

直接avg函数即可（以为一客仅会出现当月的一首单，所以单=客数）

注意round函数保留一位小数。