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

select t2.user_id, first_buy_date, second_buy_date, cnt
from
(select user_id, min(date) as first_buy_date, count(date) as cnt
from order_info
where (product_name = 'C++' or product_name = 'Python' or product_name = 'Java') and status = 'completed' and date > '2025-10-15'
group by user_id
having count(date) >= 2) as t2
join
(select user_id, date as second_buy_date
from
(select user_id, date, row_number()over(partition by user_id order by date) as rank1
from order_info
where (product_name = 'C++' or product_name = 'Python' or product_name = 'Java') and status = 'completed' and date > '2025-10-15') as t1
where rank1 = 2) as t3
on t2.user_id = t3.user_id