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

# 先关联表
with t as(
select d.order_id, 
       product_id, 
       price, 
       cnt,
       uid,
       event_time,
       date(event_time) dt,
       total_amount,
       status
from tb_order_detail d
join tb_order_overall o on d.order_id = o.order_id
),

# 确定新用户数量：根据用户分层，时间顺序，第一单时间在2021-10 里面，确定新用户的第一单

t2 as(
select order_id,product_id, uid,dt,
       CASE WHEN ROW_NUMBER() OVER(PARTITION BY uid ORDER BY event_time) = 1 
             AND DATE_FORMAT(event_time, '%Y-%m') = '2021-10'  -- 确保是10月的第一单
             THEN 1 ELSE 0 end as if_first_order
from t 
order by uid,event_time
),

# 新用户第一单的交易金额：total_amount 

#订单明细里的{该订单各商品单价×数量之和} - 订单总表里的{订单总金额} 。
t3 as (
select distinct t.order_id,
       total_amount,
       sum(t.price * t.cnt) over (partition by order_id) - total_amount  as cost
from t
join t2 on t.order_id = t2.order_id
where if_first_order = 1 
)

select round(avg(total_amount),1),round(avg(cost),1)
from t3