题解 | #10月的新户客单价和获客成本#
10月的新户客单价和获客成本
https://www.nowcoder.com/practice/d15ee0798e884f829ae8bd27e10f0d64
首先需要找到所有21年10月份的新用户的订单详情，这里每个用户有订单的最小日期即她成为新用户的日期，作为临时表1
表1已经有了订单售出总金额，还需要原价的总金额，就从另一个表获取，即构建临时表2，查询订单号和对应的原价，最后再连接两个表进行相应计算即可。
with t1 as (select * from tb_order_overall
where (uid,date(event_time)) in (select uid,min(date(event_time)) dtfrom tb_order_overall
where status=1
group by uid
having date_format(dt,'%Y-%m')='2021-10')),
t2 as (select order_id,sum(price) as self_price
from tb_order_detail
group by order_id)
select round(avg(total_amount),1) as avg_amount,
round(avg(self_price-total_amount),1) as avg_cost
from t1
join t2
on t1.order_id = t2.order_id
投递京东等公司10个岗位