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

法一：最直接的就是借鉴#牛客的课程订单分析(四)#的做法，取rk=1的表和取rk=2的表内连接起来。好处是直观，但是代码有些冗余，就不贴了。

法二：参考评论区，我想到了IF(rk=1, date, NULL)但是会出现多个user_id和NULL的date， 没想到加上一个MAX() GROUP BY 可以巧妙解决。

SELECT user_id, MAX(IF(rk=1, date, NULL)) first_buy_date,
MAX(IF(rk=2, date, NULL)) second_buy_date, cnt FROM
(SELECT user_id, date, COUNT(*) over (PARTITION BY user_id) cnt, 
 row_number() over (PARTITION BY user_id ORDER BY date) rk
 FROM order_info
 WHERE date>"2025-10-15" AND product_name IN ('C++','Java','Python') AND
 status='completed') t1
WHERE cnt>=2
GROUP BY user_id
ORDER BY user_id

法三：使用lead函数弄多一列next_date出来，然后和row_number()函数一结合取出rk=1的即可。

SELECT user_id, date AS first_buy_date, next_date AS second_buy_date, cnt FROM
(SELECT user_id, date, COUNT(*) over (PARTITION BY user_id) cnt, 
 row_number() over (PARTITION BY user_id ORDER BY date) rk,
 lead(date,1) over (PARTITION BY user_id ORDER BY date) next_date
 FROM order_info
 WHERE date>"2025-10-15" AND product_name IN ('C++','Java','Python') AND
 status='completed') t1
WHERE cnt>=2 AND rk=1
ORDER BY user_id