表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;