SELECT a.user_id,
MAX(CASE WHEN r_d=1 THEN a.date ELSE 0 END) first_buy_date,
MAX(CASE WHEN r_d=2 THEN a.date ELSE 0 END) second_buy_date,
a.cnt
FROM
(SELECT user_id,date,
dense_rank()over(partition by user_id order by date) r_d,
COUNT(*)over(partition by user_id) cnt
FROM order_info
WHERE date>'2025-10-15'
AND status='completed'
AND product_name IN ('Python','Java','C++')
) a
WHERE a.r_d IN (1,2) AND a.cnt>=2
GROUP BY a.user_id
ORDER BY a.user_id;
一开始写的很复杂,其实思路很明了,简单来看,看到first_buy_date和second_buy_date就想到用聚合函数(MAX or SUM)+CASE WHEN 语句,然后用窗口函数统计日期的排序(字段r_d)筛选出第一次和第二次购买日期。创建一个a表,表中user_id满足题意user的条件,增加r_d和cnt字段,创建表后筛选出r_d和cnt满足题意条件的,最后进行分组聚合和排序就做完了。