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

with rank_order as (
select uid,total_amount,total_cnt,status,order_id,  event_time ,  
row_number()  over(partition by uid order by event_time)  as rn
from tb_order_overall  
),
new_uid as (
select * from rank_order 
where rn=1 and date_format(event_time,'%Y-%m')  ='2021-10'
),
origin_price as (
select order_id,sum(price * cnt) as ori_cost
from tb_order_detail
group by order_id
),
diff_amount as (
select total_amount,ori_cost-total_amount as  cost
from new_uid nu 
join origin_price op on op.order_id=nu.order_id
and status=1
)
select round(avg(total_amount),1) as avg_amount,
round(avg( cost),1)  as avg_cost
from diff_amount;