题解 | #10月的新户客单价和获客成本#
10月的新户客单价和获客成本
https://www.nowcoder.com/practice/d15ee0798e884f829ae8bd27e10f0d64
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
认真仔细!