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

哎搞半天终于通过了

-- 请计算2021年10月商城里
-- 所有新用户的首***均交易金额（客单价）和平均获客成本（保留一位小数）。
-- 订单的优惠金额 = 订单明细里的{该订单各商品单价×数量之和} - 订单总表里的{订单总金额} 。
-- 选出新用户

select round(avg(订单总金额),1) avg_amount,round(avg(优惠价格),1) avg_cost
from 
(select a.uid,avg(a.total_amount) 订单总金额,sum(b.price*b.cnt)-avg(a.total_amount) 优惠价格
from tb_order_overall a left join
tb_order_detail b on a.order_id =b.order_id 
left join 
(select uid,min(event_time) mindate
from tb_order_overall
group by uid
having date_format(min(event_time),'%Y-%m')='2021-10')c
on a.uid =c.uid and a.event_time=c.mindate
where c.mindate is not null
group by a.uid) newuser_amount