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

with t1 as (
select user_id,min(date) as first_buy_date,count(product_name) as cnt
from order_info
where date>'2025-10-15'
and status='completed'
and product_name in ('C++','Java','Python')
group by user_id
having count(user_id)>=2
),
t2 as (
select user_id,date,row_number() over (partition by user_id order by date) rk
from order_info
where date >'2025-10-15'
and status='completed'
and product_name in ('C++','Java','Python')

),
t3 as (
select user_id,date
from t2
    where rk=2
)
select a.user_id,first_buy_date,date as second_buy_date,cnt
from t1 a 
left join t3 b 
on a.user_id=b.user_id
group by a.user_id
order by a.user_id