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

我的开窗函数编写+参考别人，开窗函数主要有排序和count另种

with t as(
    select user_id, date,  -- 得到满足条件的按照user_id排列的商量
    count(*) over(partition by user_id ) as cnum,
    dense_rank() over(partition by user_id order by date) as srank # 注意这里开窗函数是order by date
    from order_info
    where status='completed' 
            and date>='2025-10-15' 
            and product_name in ('C++', "Java", 'Python')
)

select t.user_id, min(date), max(date), t.cnum
from t
where t.srank<=2 and t.cnum>=2  -- 只找出每个订单的前两位！
group by user_id
order by user_id

网上第二种：在select里查询

WITH order_new AS(
    SELECT *
    FROM order_info o
    WHERE date > '2025-10-15'
    AND product_name IN ('C++', 'Java', 'Python')
    AND status = 'completed'
)

SELECT 
    user_id, 
    MIN(date) AS first_buy_date,
    (SELECT date   -- 自连接找出
     FROM order_new
     WHERE user_id = o.user_id
     ORDER BY date
     LIMIT 1, 1) AS second_buy_date,  -- 找出第二高的日期，靠limit来
    COUNT(i