题目描述: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条件外,符合其他条件的结果。添加每个用户的订单量和按日期升序排序号

select *,
    count(id) over(partition by user_id) as cnt,
    row_number() over(partition by user_id order by date) as t_rank
from order_info
where datediff(date,"2025-10-15")>0
  and status = "completed"
  and product_name in ("C++","Java","Python")

步骤二:按照排序号可知1为首次支付日,2为第二次支付日。还需要筛选出订单量>=2的结果。并且用聚合函数可以实现行转列 ( 这里min()或max()都可以)。

with re as
(
    select *,
       count(id) over(partition by user_id) as cnt,
       row_number() over(partition by user_id order by date) as t_rank
    from order_info
    where datediff(date,"2025-10-15")>0
     and status = "completed"
     and product_name in ("C++","Java","Python")
)

select user_id,
    min((case when t_rank=1 then date end))as first_buy_date,
    min((case when t_rank=2 then date end)) as second_buy_date,
    cnt
from re 
where cnt>1
group by user_id