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

方法一：窗口函数，其中最早日期，可在最后group再min(date)获得
select distinct user_id,date first_buy_date,cnt from 
(select user_id,date,
dense_rank()over(partition by user_id order by date) rk,
count(user_id)over(partition by user_id) cnt
from order_info where date >'2025-10-15' and status='completed' and product_name in ('C++','Python','Java')) a 
where a.cnt > 1 and rk = 1
order by user_id
方法二：直接聚合

select user_id,min(date) first_buy_date,count(user_id) cnt
from order_info
where date > '2025-10-15'
and product_name in ('C++','Java','Python')
and status = 'completed'
group by user_id
having count(user_id) > 1
order by user_id