SQL面试题---查询最长连续登录天数

给定Log表，查询各用户最长连续登录天数。

     `Log`   
     +----------------+---------+     
     | user_id        | int     |     
     | login_time     | date    |       
     +----------------+---------+

 

解题思路：1，给各用户的登录日期组内排序。用窗口函数ROW_NUMBER实现。

                  2，因为row_number是连续的，所以如果login_time-row_number是恒定的，说明用户从该login_time算起是连续登录的。


                  3，计算各用户login_time-row_number这个差值的数量，也就是各用户的连续登录天数。

                  4，按用户id聚合，找出各用户最大的连续登录天数。

 

 答案：

WITH day_rank AS (
    SELECT user_id, 
           login_time, 
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time) AS rank
    FROM Log),

day_diff AS (
    SELECT user_id, 
           DATE_SUB(login_time, rank) AS diff 
    FROM day_rank),

continuous_day AS (
    SELECT user_id, 
           COUNT(*) AS cnt
    FROM day_diff
    GROUP BY user_id, diff);

SELECT user_id, MAX(cnt) 
FROM continuous_day
GROUP BY user_id;