题解 | #牛客每个人最近的登录日期(三)#

SELECT 
a.user_id,
a.date as "first_buy_date",
a.cnt 
from

(SELECT
oi.user_id,
oi.date,
ROW_NUMBER()over(partition by oi.user_id order by oi.date)mm,
x.cnt
FROM order_info oi
LEFT JOIN
(select user_id,
COUNT(id) as cnt
from order_info
where datediff(date,"2025-10-15")>0
  and status ="completed"
  and product_name in("C++","Java","Python")
group by user_id
having count(id)>=2)x 
 ON x.user_id = oi.user_id

where datediff(oi.date,"2025-10-15")>0
  and oi.status ="completed"
  and oi.product_name in("C++","Java","Python")
  and x.cnt is NOT NULL)a
  where a.mm =1
ORDER BY a.user_id