Medium之1454.活跃用户***-蒲公英云

Medium之1454.活跃用户***

桃扇骨 2022-08-31 04:52 260阅读 0赞

表 Accounts:

Column Name	Type
id	int
name	varchar

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

表 Logins:

Column Name	Type
id	int
login_date	date

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

问题

写一个 SQL 查询, 找到活跃用户的 id 和 name.

活跃用户是指那些至少连续 5 天登录账户的用户.

返回的结果表按照 id 排序.

示例

Accounts 表:

id	name
1	Winston
7	Jonathan

Logins 表:

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

Result 表:

id	name
7	Jonathan

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

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/active-users
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解答

思路：
首先，需要提取同列的顺序（时间）信息进行定量而不是定性（比如只考虑大小）的比对，这个时候就应该想到需要自连接。

我们把表logins使用join自连接，条件限制在id相等和日期差距在0到4天。

筛选之后按照l1的id和login_date分组（login_date分组的原因是，按照id分完的组中所有的日期只是满足和相应id的任意一个日期差距在0到4之间，这些日期并不一定是连续的，所以我们再按照参考表l1的日期再次分组）。

最后通过count筛选，在id相同的条件下，日期相差在0到4天而且互不重复的日期合计能达到5个，这五个日期必然是连续的。

最后在select环节还需要小心用户名id的重复出现。

select distinct l1.id,name
from logins l1
    join logins l2 
    on l1.id=l2.id and datediff(l2.login_date,l1.login_date) between 0 and 4
    left join
    accounts a on l1.id=a.id 
group by l1.id,l1.login_date
having count(distinct l2.login_date) >=5
order by l1.id