sql逻辑题05

--创建表
create table user_login(
user_id varchar(20),
login_date date
);

--导入数据
insert into user_login values
('zhangsan','2013-05-06')  
,('zhangsan','2013-05-07')
,('zhangsan','2013-05-08')
,('zhangsan','2013-05-09')
,('zhangsan','2013-06-23')
,('zhangsan','2013-06-24')
,('zhangsan','2013-06-25')
,('zhangsan','2013-06-26')
,('zhangsan','2013-06-27')
,('lisi','2013-07-06')
,('lisi','2013-07-07')
,('lisi','2013-07-08')
,('lisi','2013-07-09')
,('lisi','2013-08-01')
,('lisi','2013-08-02')
,('lisi','2013-09-10')
,('wangwu','2015-09-01')
,('wangwu','2015-09-02')
,('wangwu','2015-09-03')
,('wangwu','2015-09-06')
,('wangwu','2015-09-07');

从数据中我们可以看的出来user_id为’zhangsan‘的客户最大连续登陆天数为5，’lisi‘最大登陆天数为4，'wangwu'最大登陆天数为3.

sql实现：

select t3.user_id,max(c1)
   from (
select t2.user_id as user_id
      ,t2.c       as c
      ,count(1)   as c1
      from
(
select  t1.user_id     as user_id
       ,t1.login_date  as login_date
       ,t1.rn          as rn
       ,(t1.login_date-t1.rn) c
 from(
select user_id
      ,login_date
      ,row_number() over(partition by user_id order by login_date) rn 
      from user_login
      )t1
      )t2
      group by t2.user_id,t2.c
      )t3
      group by 1

实现效果：