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;

 

上一篇:java中对象的比较(三种方式)


下一篇:pytorch 分布式训练 local_rank 问题