题解 | #10月的新户客单价和获客成本#
10月的新户客单价和获客成本
http://www.nowcoder.com/practice/d15ee0798e884f829ae8bd27e10f0d64
select round(sum(m)/count(*),1) avg_amount,round(sum(y)/count(*),1) avg_cost
from(
select uid,min(total_amount) m,sum(price*cnt) s,sum(price*cnt)-min(total_amount) y
from tb_order_overall o
join tb_order_detail d on o.order_id=d.order_id
where month(event_time)='10' and (uid,event_time) in (select uid,min(event_time)
from tb_order_overall group by uid)
group by uid
) a
from(
select uid,min(total_amount) m,sum(price*cnt) s,sum(price*cnt)-min(total_amount) y
from tb_order_overall o
join tb_order_detail d on o.order_id=d.order_id
where month(event_time)='10' and (uid,event_time) in (select uid,min(event_time)
from tb_order_overall group by uid)
group by uid
) a