题解 | #10月的新户客单价和获客成本#通俗易懂！！！！！

#指标：平均首单客单价=订单总金额/订单数
# 1.平均首单客单价
# select round(sum(total_amount)/count(distinct order_id),1) avg_amount
# from tb_order_overall
# where date_format(event_time,'%Y-%m')='2021-10'
# 2.平均获客成本=优惠总金额/订单数
# select round(sum(price-total_amount)/count(distinct  too.order_id),1)
# from tb_order_overall too
# inner join (
#   select order_id,sum(price*cnt) price
#   from tb_order_detail
#   group by order_id
#   ) as t
# on too.order_id=t.order_id
# where date_format(event_time,'%Y-%m')='2021-10'
# 3.整合+完善筛选条件
select round(sum(total_amount)/count(distinct too.order_id),1) avg_amount
,round(sum(price-total_amount)/count(distinct  too.order_id),1) avg_cost
from tb_order_overall too
inner join (
  select order_id,sum(price*cnt) price
  from tb_order_detail
  group by order_id
  ) as t
on too.order_id=t.order_id
where date_format(event_time,'%Y-%m')='2021-10'
and (uid,date(event_time)) in (select uid,min(date(event_time)) from tb_order_overall group by uid)