SQL Consecutive value meet certain threshold [closed]

I have a table like below.

Table A

|id |value1|
 --  ------
|123|    89|
|123|     8|
|123|    90|
|123|    6 |
|123|   100|
|123|    45| 
|123|    46|
|123|    77|
|123|     1|
|123|    55|
|123|     8|
|123|    65|

The goal is to find consecutive rows which are <= 60

Desired results:

|id |  value1|
 --- --------
|123|      45|
|123|      46|
|123|       1|
|123|      55|
|123|       8|

I have tried lead and lag function like other solution provided with no luck. Hope anyone may help here!

Answer

Would you try this? I’ve wrote it in BigQuery but you can understand the logic in it.

WITH data AS (
  SELECT 123 AS id, [89, 8, 90, 6, 100, 45, 46, 77, 1, 55, 8, 65] values
),
consecutives AS (
  SELECT id, value,
         IF (value <= 60 AND (
             LEAD(value) OVER (ORDER BY idx) <= 60 OR
             LAG(value) OVER (ORDER BY idx) <= 60 ), 1, 0
         ) AS is_consecutive,
    FROM data, UNNEST(values) value WITH OFFSET idx
)
SELECT * EXCEPT(is_consecutive) FROM consecutives WHERE is_consecutive = 1;

Result:
+-----+-------+
| id  | value |
+-----+-------+
| 123 |    45 |
| 123 |    46 |
| 123 |     1 |
| 123 |    55 |
| 123 |     8 |
+-----+-------+

As @tim-biegeleisen metioned, the table should have ordering column, so I’ve added it with name idx.