SQL成神之路：计算每个用户的首购和复购信息

首先还是要先想窗口函数。

别忘记first_value(),last_value()

需求：

有订单表，需计算每个用户的首次购买时间、首次购买金额、复购次数以及复购率（复购次数/总购买次数）。

schema：

data：

解决：

select 
	user_id,first_order_date,first_order_amount,repeat_orders,repeat_rate
from ( 
	select 
		user_id,
		first_value(order_date) over(partition by user_id order by order_date) as first_order_date,
		first_value(order_amount) over(partition by user_id order by order_date) as first_order_amount,
		count(1) over(partition by user_id) - 1 as repeat_orders,
		round( 
			count(1) over(partition by user_id) - 1 / count(1) over(partition by user_id),
			4
		) as repeat_rate 
	from test_xiaoyi.customer_orders2  
)a  
group by 
	user_id,first_order_date,first_order_amount,repeat_orders,repeat_rate

result：