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

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满足题意条件的，最后进行分组聚合和排序就做完了。