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

在子查询中用count()和min()搭配窗口函数算出根据user_id分组后每组的数量和最早日期。并按照题目要求限制字段。然后从子查询结果中选出所需字段并限制每组数量cnt>=2。

select distinct t.user_id, t.first_buy_day, t.cnt from
(select user_id, date, count(id) over (partition by user_id) as cnt,
 min(date) over (partition by user_id) as first_buy_day
from order_info
where (product_name = "Java" or product_name = "Python" or product_name = "C++") and 
       date > "2025-10-15" and status = "completed") as t 
where t.cnt >= 2