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

select a.user_id, min(a.date) first_buy_date, max(a.date) second_buy_date, b.cnt from
(select date, user_id,row_number() over(partition by user_id order by date) rank_
from order_info
where datediff(date,'2025-10-15')>0
and product_name in ('C++','Java','Python')
and status='completed') a
right join
(select user_id, count(*) cnt
from order_info
where datediff(date,'2025-10-15')>0
and product_name in ('C++','Java','Python')
and status='completed'
group by user_id
having count(*)>1) b
on a.user_id=b.user_id
where a.rank_=1 or a.rank_=2
group by a.user_id
order by a.user_id

• 使用窗口函数得到排名
• 使用聚合函数筛选符合条件的用户id和下单次数
• 连接两个表 注意要以第二个表为连接标准