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

# 条件：2025-10-15以后，购买成功，C++课程或Java课程或Python课程，下单2个以及2个以上的用户id。

# 思路：先筛选出符合条件的用户id。



with u as 
(
select distinct user_id
from
(
select user_id,count(user_id)over(partition by user_id) cnt
from order_info
where datediff(date,"2025-10-15") > 0
      and status = "completed"
      and product_name in ('C++','Java','Python')
) as t
where cnt >= 2
)


select user_id,first_buy_date,second_buy_date,cnt
from
(
select user_id
       ,date 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
       ,row_number()over(partition by user_id order by date) r
from order_info
where user_id in (select * from u)
      and datediff(date,"2025-10-15") > 0
      and status = "completed"
      and product_name in ('C++','Java','Python')
) a
where r = 1