https://www.nowcoder.com/practice/4ae8cff2505f4d7cb68fb0ec7cf80c57?tpId=82&&tqId=37917&rp=1&ru=/activity/oj&qru=/ta/sql/question-ranking
思路第一种，不使用窗口函数
select o1.*
from order_info o1
inner join
(select user_id
from order_info
where date>'2025-10-15' # 限定条件3个
and status='completed'
and product_name in ('C++','Java','Python')
group by user_id
having count(*)>=2) o2
on o1.user_id =o2.user_id
where o1.date>'2025-10-15' # o1中同样要限定3个条件
and o1.status='completed'
and o1.product_name in ('C++','Java','Python')
order by id
第二种，使用窗口函数
select id,user_id,product_name,status,client_id,date
from (select *,count(*) over(partition by user_id) as co
from order_info
where date>'2025-10-15' # 限定条件3个
and status='completed'
and product_name in ('C++','Java','Python')) o
where co>=2
order by id
题目2
https://www.nowcoder.com/practice/c93d2079282f4943a3771ca6fd081c23?tpId=82&tqId=37917&rp=1&ru=%2Factivity%2Foj&qru=%2Fta%2Fsql%2Fquestion-ranking
思路在上一题基础上，使用窗口函数相对比较简单
select user_id,min(date) as first_buy_date,count(*) as cnt
from (select *,count(*) over(partition by user_id) as co
from order_info
where date>'2025-10-15' # 限定条件3个
and status='completed'
and product_name in ('C++','Java','Python')) o
where co>=2
group by user_id
题目3
https://www.nowcoder.com/practice/348afda488554ceb922efd2f3effc427?tpId=82&tqId=37917&rp=1&ru=%2Factivity%2Foj&qru=%2Fta%2Fsql%2Fquestion-ranking
思路第一种，使用窗口函数lead() over()
select user_id,
min(date) as first_buy_date,
second_buy_date,
cnt
from (select *,
lead(date) over(partition by user_id order by date) as second_buy_date,
count(*) over(partition by user_id) as cnt
from order_info
where date>'2025-10-15' # 限定条件3个
and status='completed'
and product_name in ('C++','Java','Python')) o
where cnt>=2
group by user_id
第二种,对日期排序，取排名前2位的日期，最小的日期为first_buy_date，最大的日期为second_buy_date
select user_id,
min(date) as frist_buy_date,
max(date) as second_buy_date,
cnt
from (select *,
row_number() over(partition by user_id order by date) as rn,
count(*) over(partition by user_id) as cnt
from order_info
where date>'2025-10-15' # 限定条件3个
and status='completed'
and product_name in ('C++','Java','Python')) o
where cnt>=2 and rn<=2
group by user_id
