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

-- 2021年10月 ，所有新用户， 首单，平均交易金额，平均获客成本
with t1 as (select order_id,total_amount,uid,event_time,rank() over (partition by uid order by event_time ASC) dt_num
from tb_order_overall),
t2 as (select * from t1
where dt_num = 1
and substr(event_time,1,7) = '2021-10'),
t3 as (select b.order_id,sum(price * cnt) total_num
from tb_product_info a 
left join tb_order_detail b 
on a.product_id = b.product_id
left join t2
on b.order_id = t2.order_id
group by order_id)
select round(avg(total_amount),1) avg_amount,
round(avg(total_num-total_amount),1) avg_cost
from (select t3.order_id,total_amount,total_num
from t3 
inner join t2 
on t2.order_id = t3.order_id) u