题解 | #查询连续登陆的用户#

SELECT user_id
FROM
   (
    SELECT user_id
       ,DATE(log_time) AS log_date
       ,LEAD(DATE(log_time), 1) OVER(PARTITION BY user_id ORDER BY log_time) AS l1
       ,LEAD(DATE(log_time), 2) OVER(PARTITION BY user_id ORDER BY log_time) AS l2
    FROM login_tb
   ) AS t
WHERE DATEDIFF(l1,log_date) = 1
  AND DATEDIFF(l2,l1) = 1
  AND user_id IN (
                  SELECT user_id
                    FROM register_tb)
ORDER BY user_id;

参考leetcode连续问题最后解决代码：https://leetcode.cn/problems/consecutive-numbers/solutions/2832155/lian-xu-deng-lu-de-2chong-si-lu-he-yi-ge-c49e

有不正确的地方请指正

至少连续三天的条件为判断准则，只要lead一次和lead两次时间间隔为1天即符合