题目链接:点击打开链接
题目大意:略。
解题思路:注意 LEAD / LAG 函数。
AC 代码
-- 解决方案(1) WITH t1 AS (SELECT DISTINCT user_id, '2021-1-1' visit_date FROM UserVisits UNION ALL SELECT * FROM UserVisits), t2 AS(SELECT user_id, visit_date, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY visit_date) rn FROM t1) SELECT u1.user_id, MAX(DATEDIFF(u2.visit_date, u1.visit_date)) biggest_window FROM t2 u1 JOIN t2 u2 ON u1.rn + 1 = u2.rn AND u1.user_id = u2.user_id GROUP BY u1.user_id -- 解决方案(2) select user_id,max(diff) as biggest_window from( select user_id,datediff( lead(visit_date,1,'2021-01-01') over(partition by user_id order by visit_date),visit_date ) as diff from uservisits ) tmp group by user_id