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

题目仅要求返回第1、2次购买时间，故只需返回前两条记录，时间最小为第1次，时间最大为第2次，购买次数可用开窗函数计算，代码如下：

select
 a.user_id,
 min(a.date) as first_buy_date,
 max(a.date) as second_buy_date,
 a.cnt
from
    (select
     user_id,
     date,
     row_number() over(partition by user_id order by date) as rank_no,
     count(*) over(partition by user_id) as cnt
    from order_info
    where date>='2025-10-16'
      and status='completed'
      and product_name in('C++','Java','Python')
    ) a
where a.rank_no<=2 and a.cnt>=2
group by a.user_id,a.cnt
order by a.user_id ;

仅返回第1、2次购买时间属于特例，泛用的写法可在min/max内嵌套if/iif/case...when，代码如下：

select
 a.user_id,
 max(case when a.rank_no=1 then a.date else 0 end) as first_buy_date,
 max(case when a.rank_no=2 then a.date else 0 end) as second_buy_date,
 a.cnt
from
    (select
     user_id,
     date,
     row_number() over(partition by user_id order by date) as rank_no,
     count(*) over(partition by user_id) as cnt
    from order_info
    where date >= '2025-10-16'
      and status = 'completed'
      and product_name in('C++','Java','Python')
    ) a
where a.rank_no<=2 and a.cnt>=2
group by a.user_id,a.cnt
order by a.user_id ;

若不考虑泛用性，用lead/lag代替row_number也是很好的选择,代码如下：

select
 a.user_id,
 min(a.date) as first_buy_date,
 a.next_date as second_buy_date,
 count(*) as cnt
from
    (select
     * ,
     lead(date,1,0) over(partition by user_id order by date) as next_date
    from order_info
    where date>='2025-10-16'
      and status='completed'
      and product_name in('C++','Java','Python')
    ) a
group by a.user_id having count(*)>=2
order by a.user_id ;

或者

select
 a.*
from
    (select
     user_id,
     min(date) over(partition by user_id) as first_buy_date,
     lead(date,1,0) over(partition by user_id order by date) as second_buy_date,
     count(*) over(partition by user_id) as cnt
    from order_info
    where date>='2025-10-16'
      and status='completed'
      and product_name in('C++','Java','Python')
    ) a
where a.cnt>=2
group by a.user_id
order by a.user_id ;