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

问题：请计算2021年10月商城里所有新用户的首***均交易金额（客单价）和平均获客成本（保留一位小数）。

注：订单的优惠金额 = 订单明细里的{该订单各商品单价×数量之和} - 订单总表里的{订单总金额} 。

1.题目要求的指标与第一个表无关，因此我们只需要后面两个表即可完成计算。

由于要求新用户的的指标，我们先选出新用户

select * from 
(select *,rank()over(partition by uid order by event_time ) as rk  
from tb_order_overall ) as u 
where rk=1 and left(event_time,7)='2021-10'

将上述结果作为表t， 结果如下：

2.将表t和第三个表tb_order_detail连接完成我们的计算即可。 先求出total_amount,以及每个order_id的订单明细里的总和：

select t.order_id,total_amount,
sum(price*cnt) as pc
from t join tb_order_detail as c on t.order_id=c.order_id
group by t.order_id,total_amount

结果如下

剩下的按题意进行计算即可。完整代码如下

with t as 
(select * from 
(select *,rank()over(partition by uid order by event_time ) as rk  
from tb_order_overall ) as u 
where rk=1 and left(event_time,7)='2021-10')


select round(avg(total_amount),1) as avg_a,
round(avg(pc-total_amount),1) as avg_c from 
(select t.order_id,total_amount,
sum(price*cnt) as pc
from t join tb_order_detail as c on t.order_id=c.order_id
group by t.order_id,total_amount) as u