Count consecutive rows with a particular status

Question

I need to count whether there are three consecutive failed login attempts of the user in last one hour.

For example

id    userid    status  logindate
1     1         0       2014-08-28 10:00:00
2     1         1       2014-08-28 10:10:35
3     1         0       2014-08-28 10:30:00 
4     1         0       2014-08-28 10:40:00

In the above example, status 0 means failed attempt and 1 means successful attempt.

I need a query that will count three consecutive records of a user with status 0 occurred in last one hour.

I tried below query

SELECT COUNT( * ) AS total, Temp.status
FROM (
SELECT a.status, MAX( a.id ) AS idlimit
FROM loginAttempts a
GROUP BY a.status
ORDER BY MAX( a.id ) DESC
) AS Temp
JOIN loginAttempts t ON Temp.idlimit < t.id
HAVING total >1

Result:

total       status
2             1

I don't know why it display status as 1. I also need to add a where condition on logindate and status field but don't know how would it work

http://stackoverflow.com/questions/19541762/mysql-counting-the-consecutive-number-rows-that-match — NoobEditor, Aug 29 '14 at 07:29
@NoobEditor: the solution given at that post seems quite complex as i could not understand which field i need to fit in the query as per my table field. can you please guide more on this? — Neeraj, Aug 29 '14 at 07:41
Would have been easy with the `LAG` function in SQL Server.. look for something similar in mysql — Akash, Aug 29 '14 at 14:56

score 2 · Accepted Answer · answered Aug 29 '14 at 18:42

For consecutive count you can use user defined variables to note the series values ,like in below query i have use @g and @r variable, in inner query i am storing the current status value that could be 1/0 and in case expression i am comparing the value stored in @g with the status column if they both are equal like @g is holding previous row value and previous row's status is equal to the current row's status then do not change the value stored in @r,if these values don't match like @g <> a.status then increment @r with 1, one thing to note i am using order by with id column and assuming it is set to auto_increment so for consecutive 1s @r value will be same like @r was 3 for first status 1 and the again status is 1 so @r will 3 until the status changes to 0 same for status 0 vice versa

SELECT t.userid,t.consecutive,t.status,COUNT(1) consecutive_count
FROM (
SELECT a.* ,
@r:= CASE WHEN @g = a.status THEN @r ELSE @r + 1 END consecutive,
@g:= a.status g
FROM attempts a
CROSS JOIN (SELECT @g:=2, @r:=0) t1
WHERE a.`logindate` BETWEEN '2014-08-28 10:00:00' AND '2014-08-28 11:00:00'
ORDER BY id
) t
GROUP BY t.userid,t.consecutive,t.status
HAVING consecutive_count >= 3 AND  t.status = 0

Now in parent query i am grouping results by userid the resultant value of case expression i have name is it as consecutive and status to get the count for each user's consecutive status

One thing to note for above query that its necessary to provide the hour range like i have used between without this it will be more difficult to find exactly 3 consecutive statuses with in an hour

Sample data

INSERT INTO attempts
    (`id`, `userid`, `status`, `logindate`)
VALUES
    (1, 1, 0, '2014-08-28 10:00:00'),
    (2, 1, 1, '2014-08-28 10:10:35'),
    (3, 1, 0, '2014-08-28 10:30:00'),
    (4, 1, 0, '2014-08-28 10:40:00'),
    (5, 1, 0, '2014-08-28 10:50:00'),
    (6, 2, 0, '2014-08-28 10:00:00'),
    (7, 2, 0, '2014-08-28 10:10:35'),
    (8, 2, 0, '2014-08-28 10:30:00'),
    (9, 2, 1, '2014-08-28 10:40:00'),
    (10, 2, 1, '2014-08-28 10:50:00')
;

As you can see from id 3 to 5 you can see consecutive 0s for userid 1 and similarly id 6 to 8 userid 2 has consecutive 0s and they are in an hour range using above query you can have results as below

userid  consecutive  status  consecutive_count  
------  -----------  ------  -------------------
     1            2       0                    3
     2            2       0                    3

`Fiddle Demo`

Many thanks for providing the solution!! I was looking for exactly same query in which i could pass time range and number of attempts. — Neeraj, Sep 01 '14 at 05:49

score 0 · Answer 2 · answered May 14 '16 at 11:18

M Khalid Junaid's answer is great, but his Fiddle Demo didn't work for me when I clicked it.

Here is a Fiddle Demo which works as of this writing.

In case it doesn't work later, I used the following in the schema:

CREATE TABLE attempts
    (`id` int, `userid` int, `status` int, `logindate` datetime);

INSERT INTO attempts
    (`id`, `userid`, `status`, `logindate`)
VALUES
    (1, 1, 0, '2014-08-28 10:00:00'),
    (2, 1, 1, '2014-08-28 10:10:35'),
    (3, 1, 0, '2014-08-28 10:30:00'),
    (4, 1, 0, '2014-08-28 10:40:00'),
    (5, 1, 0, '2014-08-28 10:50:00'),
    (6, 2, 0, '2014-08-28 10:00:00'),
    (7, 2, 0, '2014-08-28 10:10:35'),
    (8, 2, 0, '2014-08-28 10:30:00'),
    (9, 2, 1, '2014-08-28 10:40:00'),
    (10, 2, 1, '2014-08-28 10:50:00')
;

And this as the query:

SELECT t.userid,t.consecutive,t.status,COUNT(1) consecutive_count
FROM (
  SELECT a.* ,
    @r:= CASE WHEN @g = a.status THEN @r ELSE @r + 1 END consecutive,
    @g:= a.status g
  FROM attempts a
  CROSS JOIN (SELECT @g:=2, @r:=0) t1
  WHERE a.`logindate` BETWEEN '2014-08-28 10:00:00' AND '2014-08-28 11:00:00'
  ORDER BY id
) t
GROUP BY t.userid,t.consecutive,t.status
HAVING consecutive_count >= 3 AND  t.status = 0;

Count consecutive rows with a particular status

2 Answers2

Sample data

Fiddle Demo

`Fiddle Demo`