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

# 先筛选，并进行窗口函数计算：算一个日期排序，算一个计数
select
    a.user_id as user_id,
    a.date as first_buy_date,
    a.counts as cnt
from
    (select
    *,
    dense_rank() over(partition by user_id order by date asc) as daterank,
    count(*) over(partition by user_id) as counts
    from
    order_info
    where
    date > '2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')) a
where 
    a.daterank = 1 and a.counts >= 2
order by 
    a.user_id asc;