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

#需求：计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本
#输出：avg_amount(首单的平均单价)、avg_cost(平均获客成本)
#要求：时间区间为2021年10月，对象是新用户，输出均保留一位小数；
#均单价=订单金额/用户数，平均获客成本=优惠金额/用户数，优惠金额=(price*cnt)-total_amount
#涉及数据：event_time、uid、total_amount、price、cnt，不涉及表tb_product_info，只用二表和三表链接
#拆解：时间：where date_format(event_time,'%Y%m')=202110
#新用户：开窗使用排名，rank()over(partition by uid order by event_time) ck1，排名第一的即新用户，这里与时间筛选不能做一起，否则会筛选为10月第一次购买的用户而非新用户
#均单价：avg(total_amount)，不用求用户数，平均获客成本与此同
#平均获客成本：与均单价在同一层运算，那么优惠金额需要做子查询，并且优惠金额需要依照订单做聚合
with w1 as(
    select*
    from(
        select*,rank()over(partition by uid order by event_time) ck1
        #这里意为，在tb_order_overall表上，新增了新用户列，之后以此与tb_order_detail表链接
        #开窗rank，得到排名，结合where=1，和限制时间为202110，得到10月rank=1的用户，即10月出现的新用户
        from tb_order_overall
    ) w2
    where ck1=1 and date_format(event_time,'%Y%m')=202110
)
select round(avg(total_amount),1) avg_amount,round(avg(e1-total_amount),1) avg_cost
from(
    select total_amount,sum(price*cnt) e1
    from w1 join tb_order_detail using(order_id)
    group by order_id,total_amount
) w3