Find max no of consecutive records

Question

id  || week_id || user_id || catch_id(0,1,2)
1   || 2       || 6       || 0
1   || 3       || 6       || 1
1   || 4       || 6       || 1
1   || 5       || 6       || 1
1   || 6       || 6       || 0
1   || 7       || 6       || 0
1   || 8       || 6       || 2
1   || 9       || 6       || 0
1   || 10      || 6       || 0
1   || 11      || 6       || 1

I need find out the max consecutive week the catch = 1 and max consecutive week the catch = 0 for each user (find all). I hope i make myself clear.

In the above table

max consecutive catch = 1 for user 6 is 3(weeks 3,4,5)

max consecutive weeks catch = 0 for user 6 is 2(week 6,7 and/or week 9,10)

How do i go about. Can i do this in purely sql. A php solution is also welcome

Answer 1

This should work for a SQL solution. Though it will only ever give you one week_id for the catch_id in question. I don't know what your table is called so I've called it consecutive in the answer below:

drop table if exists consecutive;

create table consecutive
(id int,week_id int,user_id int,catch_id int);

insert into consecutive values (1,2,6,0);
insert into consecutive values (1,3,6,1);
insert into consecutive values (1,4,6,1);
insert into consecutive values (1,5,6,1);
insert into consecutive values (1,6,6,0);
insert into consecutive values (1,7,6,0);
insert into consecutive values (1,8,6,2);
insert into consecutive values (1,9,6,0);
insert into consecutive values (1,10,6,0);
insert into consecutive values (1,11,6,1);

select w,count(*) as max_consecutive_weeks
from
(
select
case when @cur_catch_id != catch_id then @cur_week_id := week_id else @cur_week_id end as w,
@cur_catch_id := catch_id as catchChange,
c.*
from consecutive c
cross join (select @cur_catch_id := -1,@cur_week_id := -1) t
where user_id = 6
order by week_id asc
) t
where catch_id = 1
group by w
order by max_consecutive_weeks desc,w asc
limit 1;

You can use the same query to get max consecutive week_ids with catch_id = 0 by changing where catch_id = 1 to where catch_id = 0.

Good luck!