注意10月份的商城新用户意味着用户的首单时间在2021.10
10月的新户客单价和获客成本
https://www.nowcoder.com/practice/d15ee0798e884f829ae8bd27e10f0d64
# 先计算2021年10月平台的新用户的首单订单信息, # 此处新用户的定义是第一次下单时间在2021年10月份 with t1 as ( select b.uid, a.order_id, a.total_amount from tb_order_overall as a right join ( select * from ( select uid, min(event_time) as first_time from tb_order_overall group by uid ) as e where date_format(first_time, '%Y-%m') = '2021-10' ) as b on b.uid = a.uid and b.first_time = a.event_time ), t2 as ( # 计算每一个用户的首单订单的优惠金额 select t1.uid, t1.total_amount, d.total_cost - t1.total_amount as discount_money from t1 left join ( select t1.order_id, sum(c.price * c.cnt) as total_cost from t1 left join tb_order_detail as c on c.order_id = t1.order_id group by t1.order_id ) as d on d.order_id = t1.order_id ) select round(avg(total_amount), 1) as avg_amount, round(avg(discount_money), 1) as avg_cost from t2;