题解 | #10月的新户客单价和获客成本#
10月的新户客单价和获客成本
http://www.nowcoder.com/practice/d15ee0798e884f829ae8bd27e10f0d64
select round(avg(total_amount),1) avg_amount,round(avg(cost),1)
from (
select distinct order_id,total_amount,sale_price-total_amount cost
from tb_product_info t1 #去重
left join
(select t.order_id,event_time,product_id,uid,dense_rank()over(partition by uid order by event_time) rk,total_amount,
sum(price)over(partition by t.order_id) sale_price from tb_order_detail t
left join tb_order_overall t_1 on t.order_id=t_1.order_id) t2 #按排序找首单，统计每个订单总售价。
on t1.product_id=t2.product_id
where rk =1 and date(event_time) like '2021-10%' #按题意筛选首单以及2020年10月
) a
