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

lead窗口函数+min得到第二次购买成功日期

with tmp as(
    select user_id,date from order_info where date>'2025-10-15' and status='completed' 
and product_name in('C++','Java','Python')
)
select ui,min(date)first_buy_date,min(l),count(ui) from(select tmp.user_id ui,date,lead(date,1)over(partition by tmp.user_id order by date)l from tmp join(select user_id from tmp 
group by user_id having COUNT(tmp.user_id)>=2)t1 on tmp.user_id=t1.user_id)t2
group by ui
order by ui

不需要使用表连接筛选购买成功>=2次的，having条件可以放到最后

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 ;

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

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 ;