【SQL】牛客sql题库答案汇总(81-91)

个人提交答案汇总，part5
解答比较繁琐,有建议或好的想法欢迎评论区指出探讨=w=
1-20 答案链接在这里
21-40 答案链接在这里
41-60 答案链接在这里
61-80 答案链接在这里

81、写出一个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升序排序

select a.user_id
      ,min(case when b.user_rank = '1' then b.date end) as first_buy_date
      ,min(case when b.user_rank = '2' then b.date end) as second_buy_date
      ,a.cnt
from (
    select user_id, count(user_id) as cnt
    from order_info
    where product_name in ('C++', 'Python', 'Java') and status = 'completed'
    and date > '2025-10-15'
    group by user_id having count(user_id) >= 2
) a left join (
    select user_id, date
          ,row_number() over (partition by user_id order by date) as user_rank
    from order_info 
    where product_name in ('C++', 'Python', 'Java') and status = 'completed'
    and date > '2025-10-15'
    group by user_id, date
) b on a.user_id = b.user_id
group by a.user_id, a.cnt
order by a.user_id;

我的解答总是要繁琐点
本题我的思路是：a表查询购买2次以上的用户取出user_id和购买次数cnt，b表对所有用户的购买日期进行排序，两表进行关联取出第一次和第二次购买的日期进行输出

评论区其他精彩答案

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.