Find the people who are login 3 consecutive dates-CodePudding

LoginHistory table

 Date        Name       Login
    ----------------------------------------
    03/20/2021  Amy         1
    03/20/2021  Lily        1
    03/20/2021  Nancy       1
    03/21/2021  Amy         1
    03/21/2021  Lily        1
    03/21/2021  Leo         1
    03/22/2021  Amy         1
    03/22/2021  Lisa        1
    03/22/2021  Leo         1
    03/23/2021  Lily        1
    03/23/2021  Lisa        1
    03/23/2021  Leo         1

I want to find the people and their login date who was login instance 3 times in consecutive dates. For example, my output should has Amy, because she was login 3/20,3/21 and 3/22. For Lily, she shouldn't be in my output, because even she login 3 times, the date(3/20,3/21 and 3/23) is not in consecutive order.

output should be:

        Date        Name       Login
        ----------------------------------------
        03/20/2021  Amy         1         
        03/21/2021  Amy         1
        03/21/2021  Leo         1
        03/22/2021  Amy         1
        03/22/2021  Leo         1
        03/23/2021  Leo         1

Thanks.

CodePudding user response：

Based on the specific sample data provided, you could use analytic min and max to get the first and last date for each name, count the difference in days and the number of logins which must be 3 with 2 days between first and last date.

You haven't specific a RDBMS so the date functions may need amending as appropriate, however all RDBMS support the same functionality.

select date, name
from (
    select *, 
      DateDiff(day,Min(date) over(partition by name),  
      Max(date) over(partition by name))diff, 
      Count(*) over(partition by name) qty
    from t
)t
where diff=2 and qty=3
order by date;

CodePudding user response：

To produce a table of the consecutive logins, you can first anchor your search on the action that is the last in the sequence. Then, you can join all the preceding dates to that original result:

with vals(v) as (
   select 1
   union all
   select 2
)
select c2.* from (
  select c.* from loginhistory c where 
    (select count(*) from loginhistory c1 cross join vals v 
       where c1.name = c.name and c.dt = c1.dt   interval '1' day * v.v) = 2
) t1 
join loginhistory c2 on t1.name = c2.name and c2.dt <= t1.dt and (c2.dt   interval '2' day) >= t1.dt 
order by c2.dt

CodePudding user response：

select * from LoginHistory where name in (    
    select name
    from LoginHistory
    where date between <start> and <end> -- must be exactly three dates in the range
    group by name
    having count(distinct date) = 3
)