SQL81 牛客的课程订单分析(五)

---

一、题目

有很多同学在牛客购买课程来学习，购买会产生订单存到数据库里。

有一个订单信息表(order_info)，简况如下:

请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，

（1）那么输出这个用户的user_id，
（2）以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，
（3）以及满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date，
（4）以及购买成功的C++课程或Java课程或Python课程的次数cnt，
（5）并且输出结果按照user_id升序排序，
以上例子查询结果如下:

二、解题步骤

（1）如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程

SELECT *
FROM
	(
	 SELECT *,
	    count(id) OVER(PARTITION BY user_id) as t_rank
	    FROM order_info
	    WHERE DATEDIFF(date,'2025-10-15')>0
	          AND status='completed'
	          AND product_name in ('C++','Python','Java')
	) t
where t_rank>1;

（2）那么输出这个用户的user_id，

（3）以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，
（4）以及满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date，

（5）以及购买成功的C++课程或Java课程或Python课程的次数cnt，

SELECT 
	 #group by 所以一个userid 对应一条记录 不加min的话 默认返回第一条记录 比如不符合t_rank_1=1， 返回null。  加了max的话会对所有元素进行聚合运算，返回date
	 #分组，select除了分组字段，都要用聚合函数才行
user_id,max(if(t_rank_1=1,date,null)),max(if(t_rank_1=2,date,null)),max(t_rank)
FROM
(
    SELECT *,
    row_number() OVER(PARTITION BY user_id ORDER BY date) as t_rank_1,
    count(id) OVER(PARTITION BY user_id) as t_rank
    FROM order_info
    WHERE DATEDIFF(date,'2025-10-15')>0
          AND status='completed'
          AND product_name in ('C++','Python','Java')
) as t
WHERE t_rank>1
GROUP BY user_id

（6）并且输出结果按照user_id升序排序，

具体代码如下：

SELECT user_id,max(if(t_rank_1=1,date,null)),max(if(t_rank_1=2,date,null)),max(t_rank)
FROM
(
    SELECT *,
    row_number() OVER(PARTITION BY user_id ORDER BY date) as t_rank_1,
    count(id) OVER(PARTITION BY user_id) as t_rank
    FROM order_info
    WHERE DATEDIFF(date,'2025-10-15')>0
          AND status='completed'
          AND product_name in ('C++','Python','Java')
) as t
WHERE t_rank>1
GROUP BY user_id
ORDER BY user_id;