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

select round(sum(t2.total_amount)/count(*),1) as avg_amount
,round((sum(t3.cnt_all)-sum(t2.total_amount))/count(*),1) as avg_cost 
from 
# 查询订单表中，每个订单的金额，订单id，订单总金额
(select o2.order_id,sum(o2.price*o2.cnt) as cnt_all from tb_order_detail as o2 
group by o2.order_id ) t3
inner join 
# 查询2021年10月为新用户的信息，订单Id,用户id,购买时间，购买金额
(select t1.order_id,t1.uid,t1.event_time,t1.total_amount from 
(
select o1.order_id,o1.uid,o1.event_time,o1.total_amount,
min(o1.event_time) over (partition by o1.uid) as min_date 
from tb_order_overall as o1 ) t1 
where year(t1.event_time) = 2021 and MONTH(t1.event_time) = 10 
and t1.min_date=t1.event_time ) t2 
on t3.order_id=t2.order_id