获取用户连续登录日期

id user login_date
1 lisi 2020-07-01
2 lisi 2020-07-02
3 lisi 2020-07-03
4 lisi 2020-07-07
5 lisi 2020-07-08
6 lisi 2020-07-09
7 lisi 2020-07-10
8 lisi 2020-07-11
9 lisi 2020-07-12
10 lisi 2020-07-13
11 lisi 2020-07-14
12 lisi 2020-07-19
13 lisi 2020-07-20
14 lisi 2020-07-21
15 lisi 2020-07-24
16 lisi 2020-07-28
17 lisi 2020-07-29
18 lisi 2020-07-30
19 lisi 2020-07-31
20 lisi 2020-08-01
21 zhangsan 2020-06-10
22 zhangsan 2020-06-11
23 zhangsan 2020-06-12
24 zhangsan 2020-06-13
25 zhangsan 2020-06-15
26 zhangsan 2020-06-16
27 zhangsan 2020-06-20
28 zhangsan 2020-06-22
29 zhangsan 2020-06-23
30 zhangsan 2020-06-24
31 zhangsan 2020-06-25
32 zhangsan 2020-06-29
33 zhangsan 2020-06-30
34 zhangsan 2020-08-05
35 zhangsan 2020-08-06

select * from (
select *,
lead(login_date,1) over(partition by user order by login_date) end
from (
	select user, login_date,next_login_date,last_login_date,
		DATEDIFF(next_login_date,login_date) n_diff,
		-DATEDIFF(last_login_date,login_date) l_diff
	from (
			select user , login_date,
				lead(login_date,1,"0001-01-01") over(partition by user order by login_date) next_login_date ,
				lag(login_date,1,"0001-01-01") over(partition by user order by login_date) last_login_date
			from (
				select user, date_format(login_date,'%Y-%m-%d') login_date from retention) a ) b ) c 
where n_diff<>l_diff ) d where n_diff = 1