题解 | #牛客的课程订单分析(五)#
牛客的课程订单分析(五)
https://www.nowcoder.com/practice/348afda488554ceb922efd2f3effc427
select a.user_id, a.first_buy_date , b.second_buy_date, a.cnt from (
select user_id , date as first_buy_date, cnt from (
select user_id, date,
sum(case when status = 'completed' then 1 else 0 end) over (partition by user_id)as cnt,
rank() over (partition by user_id order by date) as rn
from order_info
where product_name in ('C++','Java','Python') and date > '2025-10-15' and status = 'completed'
) t where t.rn = 1 and t.cnt>= 2 order by user_id ) a join
(select user_id , date as second_buy_date, cnt from (
select user_id, date,
sum(case when status = 'completed' then 1 else 0 end) over (partition by user_id)as cnt,
rank() over (partition by user_id order by date) as rn
from order_info
where product_name in ('C++','Java','Python') and date > '2025-10-15' and status = 'completed'
) t where t.rn = 2 and t.cnt>= 2 order by user_id ) b
on a.user_id = b.user_id and a.cnt = b.cnt
写的有点丑陋
科大讯飞公司氛围 434人发布
