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

select t.user_id, t.date as first_buy_date ,t.cnt from
(select *,row_number() over (partition by user_id order by date ) as rk,count(product_name) over (partition by user_id ) as cnt
from order_info 
 where date > '2025-10-15'
   and status ='completed'
   and product_name in ('C++','Python','Java')) as t

where t.rk=1 and t.cnt>=2
order by user_id

2种做法

法一：简单

基于订单分析二做聚合

法二：窗口函数--2个注意点

row_number() over (partition by user_id order by date ) as rk---rk=1 是初次购买日期 first_buy_date

count(product_name) over (partition by user_id ) as cnt--cnt>=2 【对用户分组再计算该用户购买课程数count(product_name)】