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

思路：

变量选取

1. 2021年10月所有——以月份分组计算

• date_format(event_time,'%Y%m') as month
• where month='202110
• group by month

2. 2021年10月新用户——首单在2021.10

• min(event_time) as first_time在2021.10

3. 首单

• event_time=first_time

4. 订单优惠金额

• 应付：sum(price*cnt) as total_price group by order_id
• 实付：total_amout

步骤拆分

1. 筛选首单信息

• 用于筛选符合条件的首单需要的month,first_time
• 用于计算金额的total price,total_amout
• 前者group by uid，后者group by order_id，故可用聚合窗口函数，互不干扰

2. 计算平均首单客单价和平均获客成本

• avg取平均
• group by month

代码

select round(avg((total_amount)),1) as avg_amount,
round(avg(total_price-total_amount),1) as avg_cost
from
    (select distinct tod.order_id,event_time,total_amount,
    date_format(event_time,'%Y%m') as month,
    min(event_time) over (partition by uid) as first_time,
    sum(price*cnt) over (partition by tod.order_id) as total_price
    from tb_order_detail as tod
    join tb_order_overall as too
    on tod.order_id=too.order_id) as q
where month='202110' and event_time=first_time
group by month