题解 | #10月的新户客单价和获客成本#尤其通俗易懂版！结构化逻辑清楚

select round(avg(total_amount),1) as avg_amount,
			 round(avg(price-total_amount),1) as avg_cost
from (
		select uid,order_id,total_amount,price,date(event_time) as dt,min_date
		from tb_order_overall
		left join (select uid,min(date(event_time)) as min_date
							 from tb_order_overall
							 group by uid) as t1 using(uid)
		left join (select order_id,sum(price*cnt) as price
							 from tb_order_detail
							 group by order_id) as t2 using(order_id)
		where date_format(event_time,'%Y%m')='202110' and datediff(min_date,date(event_time))=0  #确保是新用户
		 ) as t1