1. 题目
-
题目来源:SQL81 牛客的课程订单分析(五)
-
题目描述
# 写出一个sql语句查询在2025-10-15以后, # 如果有一个用户下单2个以及2个以上 # 状态为购买成功的C++课程或Java课程或Python课程, # 那么输出这个用户的user_id,以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date, # 以及满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date, # 以及购买成功的C++课程或Java课程或Python课程的次数cnt,并且输出结果按照user_id升序排序
-
一张表
-
结果要求
2. 解题
思路:根据题目要求抽丝剥茧
1、主表需要根据user_id
进行分组
2、主表的行过滤条件:
① 查询在2025-10-15以后
② 用户下单2个以及2个以上
③ 状态为购买成功
④ C++课程或Java课程或Python课程
以上条件,除了第②个,通过 WHERE语句
简单过滤即可,
关键是【用户下单2个以及2个以上】这个条件如何满足,这里需要对用户进行”分区“,所以考虑使用COUNT()窗口函数:在子查询中新增cnt列
,然后在外层WHERE
中进行条件限制。
(也可以使用单纯的子查询来完成,可以参考之前的博客:SQL83 牛客的课程订单分析(七))
3、从主表中SELECT出来的三个字段(除了user_id
)包括:
① 【第一次购买成功课程的日期first_buy_date
】,在外表直接使用MIN()函数
即可
② 【第二次购买成功的日期second_buy_date
】,在子查询中使用LEAD()函数
或者 (分组内容)只筛选出两行之后在在外层表格中使用MAX()函数
,这里采用后者
③ 【购买成功的次数cnt
】,使用COUNT()函数
即可
所以题目主要是考察跟日期函数、聚合函数以及窗口函数相关的一些内容。
(注意:窗口函数的值想要用于WHERE语句,必须先将其放在子查询中)
直接上代码:
SELECT t.user_id,
MIN(t.date) first_buy_date,
# DATEADD(DAY, 1, MIN(t.date)), # MySQL不支持
# DATE_ADD(MIN(t.date), INTERVAL 1 DAY), # MySQL支持
MAX(t.date) second_buy_date,
t.cnt
FROM (SELECT *,
COUNT(*) OVER(PARTITION BY user_id) cnt,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) rk_num
FROM order_info
WHERE date > '2025-10-15'
AND status = 'completed'
AND product_name IN ('C++', 'Java', 'Python')
) t
WHERE t.cnt >= 2 AND t.rk_num <= 2
GROUP BY t.user_id
ORDER BY t.user_id ASC