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

with temp1 as(
select order_id,uid	,event_time,status,row_number()over(partition by uid order by event_time) rk,total_amount
from tb_order_overall),
temp2 as (
select order_id,uid	,total_amount
from temp1
where rk=1 and year(event_time) = 2021 and month(event_time) = 10 and status = 1),
temp3 as(
select order_id ,sum( price * cnt ) amount
from tb_order_detail
group by order_id)
select round(sum(total_amount)/count(order_id),1) avg_amount ,round(sum(amount-total_amount)/count(order_id),1) avg_cost
from temp2 join temp3 using(order_id)