题解 | #牛客的课程订单分析(五)#

select a.user_id,a.date as first_buy_date,b.date as second_buy_date,a.count as cnt
from(
(select user_id,product_name,count(user_id) over  (partition by 
user_id)as count,date,row_number() over (partition by user_id order by date asc) as rk
from order_info
where date > '2025-10-15' and status = 'completed' and product_name != 'JS') as a
inner join 
(select user_id,product_name,count(user_id) over  (partition by 
user_id)as count,date,row_number() over (partition by user_id order by date asc) as rk
from order_info
where date > '2025-10-15' and status = 'completed' and product_name != 'JS') as b
on a.user_id = b.user_id and a.rk = 1 and b.rk = 2
# group by a.user_id
)