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

# 先筛选，并进行窗口函数计算：算一个日期排序，算一个计数
select
    distinct
    a.user_id as user_id,
    min(a.date) over(partition by user_id) as second_buy_date,
    max(a.date) over(partition by user_id) 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 in(1,2) and a.counts >= 2
order by 
    a.user_id asc;