题解 | #10月的新户客单价和获客成本#

select round(avg(total_amount),1) as avg_amount, round(avg(yh_amount),1) as avg_cost -- round(sum(total_amount) / count(t1.order_id), 1) as avg_amount, -- round(sum(yh_amount) / count(t1.order_id), 1) as avg_cost from tb_order_overall t1 left join ( -- 计算每单的优惠金额 select t1.order_id, sum(t2.price * t2.cnt) - sum(distinct t1.total_amount) as yh_amount from tb_order_overall t1 left join tb_order_detail t2 on t1.order_id = t2.order_id group by t1.order_id ) t2 on t1.order_id = t2.order_id where (uid, event_time) in ( -- 限定为在10月的首单 select uid, min(event_time) as min_dt from tb_order_overall group by uid having substr(min(event_time), 1, 7) = '2021-10' )