SQL 81） 牛客的课程订单分析(五)

新增限制条件

• 第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date

参考了友友的答案，where筛选max min 真的很妙
但不太理解为什么要group by...希望有大神解释一下 TVT

select a.user_id,
min(date) as first_buy_date,
max(date) as second_buy_date,
a.cnt
from
(select user_id , date, row_number()over(partition by user_id order by date asc) r_number ,
count(id)over(partition by user_id)cnt
from order_info
where date > '2025-10-15'
and product_name in ('C++','Java','Python')
and status = 'completed')a
where a.r_number <= 2
and a.cnt >= 2
group by a.user_id , a.cnt
order by a.user_id asc;