题解 | #牛客的课程订单分析(五)#

select g1.user_id,first_buy_date,second_buy_date,cnt
from (select user_id,count(*) as cnt
from order_info
where status='completed' and 
product_name in ('C++','Java','Python')
and date > '2025-10-15'
group by user_id
having count(*) >1)g1
left join (select user_id,date as first_buy_date	
from (select *,rank() over(partition by user_id order by date) mm
from order_info
where status='completed' and 
product_name in ('C++','Java','Python')
and date > '2025-10-15'
)h
where mm=1
)g2 on g1.user_id=g2.user_id
left join (select user_id,date as second_buy_date
from (select *,rank() over(partition by user_id order by date) mm
from order_info
where status='completed' and 
product_name in ('C++','Java','Python')
and date > '2025-10-15'
)h
where mm=2)g3 on g1.user_id=g3.user_id
order by g1.user_id



# select user_id,
#     min(date) as first_buy_date,
#     min(下一个日期) as second_buy_date,
#     max(次数) as cnt
# from (
#     select *,
#         row_number() over(partition by user_id order by date) as 次数,
#         lead(date,1) over(partition by user_id order by date) as 下一个日期
#     from order_info
#     where date>'2025-10-15'
#     and status='completed'
#     and product_name in ('Python','Java','C++')
#     order by user_id) as t
# group by user_id
# having count(*)>=2
# order by user_id;