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

with t1 as 
(select uid, min(event_time) as first_order_time from tb_order_overall
group by uid),--得到用户首单购买时间表格
t2 as (select order_id, sum(price*cnt) as total_price
from tb_order_detail
group by order_id)--得到每个订单的商品价格
select round(avg(total_amount),1) as avg_amount, --平均交易金额
round(avg(total_price - total_amount),1) as avg_cost--平均交易成本
from t1 left join tb_order_overall t3 on t1.uid = t3.uid
left join t2 on t2.order_id = t3.order_id
where year(first_order_time) = '2021' --限制时间范围
and month(first_order_time) = 10
and event_time = first_order_time--限制首单购买