实现查询连续3天登陆的用户

sql写出连续三天都登录的用户

1、创建表

create table test_user_login_3days(
user_id int,
login_date string
);

2、数据准备

insert into test_user_login_3days values (123,'2018-08-02');
insert into test_user_login_3days values (123,'2018-08-03');
insert into test_user_login_3days values (123,'2018-08-04');
insert into test_user_login_3days values (456,'2018-11-02');
insert into test_user_login_3days values (456,'2018-12-09');
insert into test_user_login_3days values (789,'2018-01-01');
insert into test_user_login_3days values (789,'2018-04-23');
insert into test_user_login_3days values (789,'2018-09-10');
insert into test_user_login_3days values (789,'2018-09-11');
insert into test_user_login_3days values (789,'2018-09-12');

3、查询逻辑一：

思路：连续三天的逻辑变更为按照user_id分组排序之后，前后两个日期相减为1，再相加等于3

select
B.user_id
from
(
select
A.user_id,
A.login_date,
date_sub (A.login_date,A.rn) AS inteval_days
from
(
select
user_id,
login_date,
row_number() over (partition by user_id order by login_date) as rn
from
test_user_login_3days)A)B
group by B.user_id,B.inteval_days
having count(1) = 3;

 4、查询逻辑二：第三天减去第一天的差为2，只要存在差为2的都是满足连续三天登录的情况

SELECT
A.user_id,A.login_date,A.lag_2days
FROM (SELECT
user_id,
login_date,
LEAD(login_date,2,'9999-12-31') OVER(PARTITION BY user_id ORDER BY login_date) AS lag_2days
FROM test_user_login_3days
ORDER BY user_id,login_date)A
WHERE DATEDIFF(A.lag_2days,A.login_date) = 2;

同理，这种情况可以继续推到满足连续N天登录的情况。