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

# 商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（平均优惠价）。
# 新用户的定义？之前（10月份前）没有出现过的uid
# 字段：客单价 / 平均获客成本

# 表一：聚合订单明细表
with table1 as (
    select order_id,sum(price*cnt) as  pre_cost
    from tb_order_detail
    group by order_id
),

# 表二：连接 订单总表 & 新用户筛选
table2 AS(
    select event_time,order_id,uid,total_amount,
    row_number()OVER(partition by uid order by event_time asc) as rk,
    # COALESCE(pre_cost, 0) - total_amount AS cost
    (pre_cost - total_amount) as cost
    from tb_order_overall left join table1 using(order_id)
    where uid not in(select uid from tb_order_overall where date(event_time)<'2021-10-01') AND status = 1 

)


# 计算客单价 & 平均获客成本
select ROUND(sum(total_amount)/count(distinct order_id),1) AS avg_amount,
ROUND(sum(cost)/COUNT(DISTINCT order_id),1) AS avg_cost
FROM table2
WHERE YEAR(event_time) = 2021 AND MONTH(event_time) = 10 AND rk = 1


# 注意点：计算客单价的时候不能用avg，而是sum/count
# 注意点：首单！ 需要先对table2中的数据进行uid分组，时间排序。。。rk = 1