方法一：用min()和max()确定第一名和第二名，我感觉这种不是最好的方法，如果需要确定第三名、第四名，用这种方法就不好实现了。

with base_table as (
select id,user_id,product_name,status,client_id,date,
row_number() over(partition by user_id order by date asc) as row_num,
count1
from(
select id,user_id,product_name,status,client_id,date,
count(*) over(partition by user_id) as count1
from order_info
where status = 'completed' and product_name in ('c++','python','java') and date >'2025-10-15' ) as t1
where count1 >=2
)

select user_id,min(date) as first_buy_date,
max(date) as second_buy_date,
count1 as cnt
from base_table
where row_num in ('1','2')
group by user_id
order by user_id

方法二：解决了方法一的局限，使用了inner join，大数据量查询效率较低。

with base_table as (
select id,user_id,product_name,status,client_id,date,
row_number() over(partition by user_id order by date asc) as row_num,
count1
from(
select id,user_id,product_name,status,client_id,date,
count(*) over(partition by user_id) as count1
from order_info
where status = 'completed' and product_name in ('c++','python','java') and date >'2025-10-15' ) as t1
where count1 >=2
)

select t_first.user_id,t_first.date,t_second.date,t_first.count1 
from 
(select user_id,date,
count1 
from base_table
where row_num = 1) as t_first
inner join
(select user_id,date,
count1 
from base_table
where row_num = 2) as t_second
on t_first.user_id=t_second.user_id

方法三：使用case when

with base_table as (
select id,user_id,product_name,status,client_id,date,
row_number() over(partition by user_id order by date asc) as row_num,
count1
from(
select id,user_id,product_name,status,client_id,date,
count(*) over(partition by user_id) as count1
from order_info
where status = 'completed' and product_name in ('c++','python','java') and date >'2025-10-15' ) as t1
where count1 >=2
)

select user_id,
max(case when row_num=1 then date end) as first_buy_date,
max(case when row_num=2 then date end) as second_buy_date,
count1
from 
base_table
group by user_id