思路:

这道题要明白窗口函数和 group by 的区别，窗口函数将所有数据查出来，group by 则取出一条

第一步: 先用窗口函数取出符合条件的用户

select * ,count(id) over(partition by user_id) from order_info
where date>'2025-10-15'and status ='completed' and product_name in('Java','C++','Python');

+----+---------+--------------+-----------+-----------+------------+--------------------------------------+
| id | user_id | product_name | status    | client_id | date       | count(id) over(partition by user_id) |
+----+---------+--------------+-----------+-----------+------------+--------------------------------------+
|  4 |      57 | C++          | completed |         3 | 2025-10-23 |                                    2 |
|  6 |      57 | Java         | completed |         1 | 2025-10-24 |                                    2 |
|  5 |  557336 | Java         | completed |         1 | 2025-10-23 |                                    2 |
|  7 |  557336 | C++          | completed |         1 | 2025-10-25 |                                    2 |
+----+---------+--------------+-----------+-----------+------------+--------------------------------------+
4 rows in set (0.00 sec)

第二步: 筛选出number >= 2 的

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