题解 | #牛客的课程订单分析(五)#
牛客的课程订单分析(五)
http://www.nowcoder.com/practice/348afda488554ceb922efd2f3effc427
1.获得每个用户每个订单信息、购买次数、按日期的排序
select *,count(user_id)over(partition by user_id) cnt,Row_number()over(partition by user_id order by date) r
from order_info
where DATEDIFF(date,'2025-10-15') >0
and product_name in ('C++','Java','Python')
and status ='completed';t1
2.获得购买次数为2及以上，按日期排序小于等于2的订单（此时每个符合要求的用户只有两个订单），筛选之后的订单用开窗函数获得最小的日期作为first_buy_date，最大的日期作为second_buy_date即可。
select distinct user_id,
min(date)over(partition by user_id) first_buy_date,
max(date)over(partition by user_id) second_buy_date,
cnt
from (select *,count(user_id)over(partition by user_id) cnt,Row_number()over(partition by user_id order by date) r
from order_info
where DATEDIFF(date,'2025-10-15') >0
and product_name in ('C++','Java','Python')
and status ='completed')t1
where cnt>=2
and r<=2
