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

with
    rankList as (
        select * from (
        select
            order_id,
            uid,
            total_amount,event_time,
            row_number() over (
                partition by
                    uid
                order by
                    event_time
            ) as rank_num
        from tb_order_overall
            tb_order_overall
    )t1
    where rank_num = 1
    and left(event_time,7) = '2021-10'

) #这块查询是根据下单时间排名最早的来获取买家uid和order_id的,rank_num = 1判断是否为首次下单left(event_time,7) = '2021-10' 判断是否是10月份的首次下单
,   detailList as (
    select order_id,sum(price*cnt) as pc ,total_amount  from 
    rankList join tb_order_detail  using (order_id)
    group by order_id,total_amount
)#根据order_id,total_amount 来得出原先的客单价和折扣总价
select
    round(avg(total_amount),1),#平均的客单价
    round(avg(pc-total_amount),1) #平均的获客成本
    from
    detailList

本题难度在于以下几点

1.如何求出新用户首次下单，那么得根据订单详情排序row_number()over(partition by uid order by event_time) 的返回值为1，来获取到每个客户的最早下单情况，把下单时间控制下2021年10月。

2.再根据筛选后的order_id与订单明细标的order_id对连，就能知道每个order_id下面的商品价(price*cnt)和折扣总价total_amount，然后将order_id 和total_mount聚合后得出