Extracting rows with a consecutive pattern that exceeds a threshold

Question

I currently have this set of data.

Id	pattern
1	1
2	2
3	1
4	2
5	3
6	4
7	1
8	2
9	1
10	2
11	3

I have ids sorted ascending, and a column called pattern. The goal is to retrieve only the rows with a consecutive pattern >=3 down the table.

So the result would be:

Id	pattern
3	1
4	2
5	3
6	4
9	1
10	2
11	3

Since the pattern for Ids 3-6 have consecutively reached >=3, we will return these 4 rows. Same goes for Ids 9-11.

Id 1-2 did not reach the threshold of 3. Same for Id 7-8.

Would appreciate any help!

Answer 1

Use SUM() window function to create the groups of the sequences and with MAX() window function check the max pattern in each group:

SELECT Id, pattern
FROM (
  SELECT *, MAX(pattern) OVER (PARTITION BY grp) max_pattern
  FROM (
    SELECT *, SUM(pattern = 1) OVER (ORDER BY Id) grp
    FROM tablename
  ) t
) t
WHERE max_pattern >= 3;

Or, with a correlated subquery, for versions of MySql that do not support window functions:

SELECT t1.*
FROM tablename t1
WHERE (SELECT t2.pattern FROM tablename t2 WHERE t2.Id = t1.Id + (3 - t1.pattern)) = 3; 

See the demo.