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

select round(avg(total_amount),1) avg_amount,round(avg(cost),1) avg_cost
from(
with a1 as(
select uid,order_id
from(
select uid,order_id
,rank()over(partition by uid order by event_time) ranking
from tb_order_overall
) a where ranking = 1
)
select t1.order_id,any_value(total_amount) total_amount,sum(price*cnt)-any_value(total_amount) cost
from a1 t1 
left join tb_order_overall t2
on t1.order_id = t2.order_id and t1.uid = t2.uid
left join tb_order_detail t3
on t1.order_id = t3.order_id
where event_time between '2021-10-01' and '2021-10-31'
group by t1.order_id) b;