题解 | #牛客的课程订单分析(五)#
牛客的课程订单分析(五)
http://www.nowcoder.com/practice/348afda488554ceb922efd2f3effc427
select r1.user_id, r1.first_buy_date, r2.date, r1.cnt from
(
select user_id, min(date) first_buy_date, count(*) cnt from
(select user_id, date, rank() over(partition by user_id order by date) r from order_info
where user_id in
(select user_id from order_info
where date>'2025-10-15' and status = 'completed' and product_name in ('C++', 'Java', 'Python')
group by user_id
having count(*)>=2)
and date>'2025-10-15' and status = 'completed' and product_name in ('C++', 'Java', 'Python')
order by user_id, date
) re
group by user_id
order by user_id
) r1
join
(select user_id, date, rank() over(partition by user_id order by date) r from order_info
where user_id in
(select user_id from order_info
where date>'2025-10-15' and status = 'completed' and product_name in ('C++', 'Java', 'Python')
group by user_id
having count(*)>=2)
and date>'2025-10-15' and status = 'completed' and product_name in ('C++', 'Java', 'Python')
order by user_id, date
) r2
on r1.user_id=r2.user_id
where r2.r=2
