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

select distinct t1.user_id
,min(date) over(partition by t1.user_id) as first_buy_date
,t1.count_cnt from (
select user_id,date,status,product_name
,count(if((date>='2025-10-15'
          and status='completed'
          and (product_name='C++' 
               or product_name='Java' 
               or product_name='Python')),1,null))
               over(partition by user_id) as count_cnt 
               from order_info) t1 
               where t1.count_cnt >= 2 
               and t1.date>='2025-10-15'
          and t1.status='completed'
          and (t1.product_name='C++' 
               or t1.product_name='Java' 
               or t1.product_name='Python')
               order by t1.user_id