登录连续问题

写一个 SQL 查询, 找到活跃用户的 id 和 name，活跃用户是指那些至少连续 5 天登录账户的用户，返回的结果表按照 id 排序.

结果表格式如下例所示:

常规思路会是如下的自连接：

select id, name
from Accounts
where id in (
    select distinct a.id 
    from Logins a, Logins b, Logins c, Logins d, Logins e
    where datediff(b.login_date, a.login_date) =1
    and datediff(c.login_date, b.login_date) =1
    and datediff(d.login_date, c.login_date) =1
    and datediff(e.login_date, d.login_date) =1
    and a.id=b.id
    and b.id=c.id
    and c.id=d.id
    and d.id=e.id
)
order by id

但是如果天数增加的话，那自连接的表的数量也会随着增加，所以n较大时，尽量采用另一种方法：

这里考察的是至少连续登录n天，如果仅仅是连续登录指定天数n的话，写起来会比较简单:

group by id
having count(*)=n and max(login_date)-min(login_date)=n-1

看评论区的答案，都很长，确实值得学习，写SQL的重点还是解题思路上，比如下面的这个：


SELECT t4.id, name
FROM
(
    SELECT DISTINCT id
    FROM
    (
        SELECT id, SUBDATE(login_date, rn) AS diff
        FROM
        (
            SELECT
                id
                ,login_date
                ,ROW_NUMBER() OVER(PARTITION BY id ORDER BY login_date) AS rn
            FROM
            (
                SELECT id, login_date
                FROM Logins
                GROUP BY id, login_date
            ) t1
        ) t2
        GROUP BY id, diff
        HAVING COUNT(*) >= 5
    ) t3
) t4
LEFT JOIN Accounts t5 ON t4.id = t5.id
ORDER BY id
;

一层层运行的结果如下：

其实到达这一步，也就是第三个select写完后，基本明朗了，我们可以如下语句筛选出来符合条件的

GROUP BY id, diff
        HAVING COUNT(*) >= 5

刷中等题中。。。

登录连续问题

浏览过的版块