#订单明细中的该订单各商品单价×数量之和
with a as(
select order_id,
price*cnt as money
from tb_order_detail)

#新用户
select round(avg(user_amount),1) as avg_amount,
round(avg(user_cost),1) as avg_cost
from(

select uid,avg(total_amount) as user_amount,
sum(money)-avg(total_amount) as user_cost
from(

select b.uid,b.total_amount,a.money from(

select uid,
event_time ,
row_number() over(partition by uid order by event_time) as rk,
order_id,
total_amount
from tb_order_overall 
where status =1

)b

 join a on a.order_id = b.order_id
where rk=1
and date_format(event_time,'%Y-%m') ='2021-10' )c

group by uid)d

1）2021-10新用户的首单的相关信息（uid,

先按照event_time对订单进行排序，结果输出为b

在b的基础上限制rk=1 实现首单，date_format(event_time,'%Y-%m') ='2021-10'实现十月的首单，即10月的新用户

(
select uid,
event_time ,
row_number() over(partition by uid order by event_time) as rk,
order_id,
total_amount
from tb_order_overall 
where status =1

)b

 join a on a.order_id = b.order_id
where rk=1
and date_format(event_time,'%Y-%m') ='2021-10' )c

2）按order_id聚合计算该订单各商品单价×数量之和

#订单明细中的该订单各商品单价×数量之和
with a as(
select order_id,
price*cnt as money
from tb_order_detail)

3）将a与2021-10新用户的首单的相关信息联立，得到每个用户的首单交易金额（客单价）--这里要有avg和获客成本

select uid,avg(total_amount) as user_amount,
sum(money)-avg(total_amount) as user_cost
from(
select b.uid,b.total_amount,a.money from(

select uid,
event_time ,
row_number() over(partition by uid order by event_time) as rk,
order_id,
total_amount
from tb_order_overall 
where status =1

)b

 join a on a.order_id = b.order_id
where rk=1
and date_format(event_time,'%Y-%m') ='2021-10' )c

4)最终计算用户的平均交易金额和平均成本