连续3天日活问题与答案

表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 是活跃用户.

-- mysql答案
SELECT DISTINCT A.*
FROM Accounts A
JOIN logins L1 USING(id)
JOIN Logins L2 ON L1.id=L2.id AND DATEDIFF(L2.login_date,L1.login_date) BETWEEN 0 AND 4
-- 对每个id相同的日期都只连接相差4天之内的日期。即对每个日期都检索一下是否有5个连续的日期。
-- 只要5天之内，日期有一个不连续，那么去重后每一组的天数必然少于5天。
GROUP BY A.id,A.name,L1.login_date
HAVING COUNT(DISTINCT L2.login_date)=5

--hive答案
select
name
from
(select
name,
date_sub(logindate,rn) as rndate,
count(1) as co
from (
select
name,
logindate,
row_number() over(partition by name order by logindate) as rn
from login )a
group by name,rndate)b where co >1;