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

with temp1 as(
# 1. 2021年10月商城里所有新用户的首单实际付款
select order_id, uid, total_amount from(
    select order_id, uid, event_time, 
    min(event_time) over(partition by uid) as first_time, total_amount 
    from tb_order_overall
    where status = 1 
)t
where event_time = first_time and year(first_time)=2021 and month(first_time)=10
), temp2 as(
# 2. 2021年10月商城里所有新用户的首单原价
    select temp1.order_id, uid, total_amount, (raw_price-total_amount) as cut from temp1
    left join (
        select order_id, sum(price*cnt) as raw_price from tb_order_detail
        group by order_id
    )t2
    on temp1.order_id = t2.order_id
)

select round(avg(total_amount),1) as avg_amount,
round(avg(cut),1) as avg_cost
from temp2

认真仔细！