SQL练习题：连续登录5天的活跃用户

题目

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

Accounts 表:
id 是该表主键.
该表包含账户 id 和账户的用户名.

id	name
1	Winston
7	Jonathan

Logins 表:
该表无主键, 可能包含重复项.
该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次.

id	login_date
7	2020-05-30
1	2020-05-30
7	2020-05-31
7	2020-06-01
7	2020-06-02
7	2020-06-02
7	2020-06-03
1	2020-06-07
7	2020-06-10

结果表格式如下例所示:

id	name
7	Jonathan

id = 1 的用户 Winston 仅仅在不同的 2 天内登录了 2 次, 所以, Winston 不是活跃用户.
id = 7 的用户 Jonathon 在不同的 6 天内登录了 7 次, , 6 天中有 5 天是连续的, 所以, Jonathan 是活跃用户.

---

解法一 窗口函数lead +datediff

1）去重
因为登录表汇总了多个日期和id 号，同一天可能回登录多次，我们只需要一个登录日期。所以需要先group by id, login_date去重。
2）datediff +lead
再用lead(login_date,4)over()窗口函数查找往下第4个登录日期是否与当前日期相差4天，即连续5天。

select distinct t.id,a.name
from (
    select
    	id, 
    	login_date,
    	datediff(
        	lead(login_date, 4) over(partition by id order by login_date)
        	,login_date
        	) as diff   
    from logins
    group by id, login_date
    ) as t
left join accounts a using(id)
where t. diff = 4

解法二：自连接

logins自连接后，限制条件有：
1） id 相同；
2）日期相差0-4天。
最后找出符合条件的日期合计达到5个的id。

如下表所示，如果有连续登录5天，那么在diff列在0-4范围内的会有5行。
连续登录5天的情况：

date	date2	diff
t	t	0
t	t-1	1
t	t-2	2
t	t-3	3
t	t-4	4

第二天没有登录的情况：

date	date2	diff
t	t	0
t	t-1	1
t	t-3	3
t	t-4	4

select distinct a.id,a.name
from (
        select a.id, a.login_date as ad, b.login_date as bd
        from logins a
        join logins b
        	on a.id = b.id and datediff(a.login_date,b.login_date) between 0 and 4
        group by a.id, a.login_date
        having count(distinct b.login_date) = 5) as t 
left join accounts a on a.id = t.id

来源：力扣（LeetCode） 链接：https://leetcode.cn/problems/active-users