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

第一步，因为要在满足日期、订单状态、课程类别的where筛选基础上进一步选出满足以上条件的最早日期和订单次数，所以需要通过窗口函数新增日期排序和订单次数字段，即
(SELECT user_id, date,
           COUNT(*) OVER (PARTITION BY user_id) AS r,
           ROW_NUMBER() OVER (PARTITION BY user_id order by date) AS rn
    FROM order_info
    WHERE date > '2025-10-15'
      AND status = 'completed'
      AND product_name IN ('C++', 'Python', 'Java')) as a
第二步，在第一步基础上，对日期排序和订单次数字段进行where筛选，并对最终结果按order by user_id排序，即SELECT user_id,date as first_buy_date,a.r as cnt
from
(SELECT user_id, date,
           COUNT(*) OVER (PARTITION BY user_id) AS r,
           ROW_NUMBER() OVER (PARTITION BY user_id order by date) AS rn
    FROM order_info
    WHERE date > '2025-10-15'
      AND status = 'completed'
      AND product_name IN ('C++', 'Python', 'Java')) as a
      where a.r>=2
      and a.rn=1
      order by user_id