Find transitions from exact values SQL

There is a dataset. I would like to take first transitions from class not in (Other, Others) to class in (Other, Others)

| row_id | class   |
| ------ | ------- |
| 1      | Math    |
| 2      | Math    |
| 3      | Math    |
| 4      | Math    |
| 5      | Math    |
| 6      | Math    |
| 7      | Other   |
| 8      | Other   |
| 9      | Other   |
| 10     | Biology |
| 11     | Biology |
| 12     | Other   |
| 13     | Other   |
| 14     | Biology |
| 15     | Biology |
| 16     | Others  |
| 17     | Others  |
| 18     | Others  |
| 19     | Physics |
| 20     | Others  |

So the result will be :

| row_id | class   | prev_row_id | prev_class |
| ------ | ------- | ----------- | ---------- |
| 6      | Math    | 7           | Other      |
| 11     | Biology | 12          | Other      |
| 15     | Biology | 16          | Other      |
| 19     | Physics | 20          | Others     |

I have found how to detect LAST transition but not in history. I use PTRESTO.

We can use the LEAD() analytic function here:

<!-- language: sql -->

WITH cte AS (
    SELECT *, LEAD(class) OVER (ORDER BY row_id) AS lead_class,
              LEAD(row_id) OVER (ORDER BY row_id) AS lead_row_id
    FROM yourTable
)

SELECT
    row_id,
    class,
    lead_row_id AS next_row_id,
    lead_class AS next_class
FROM cte
WHERE class NOT IN ('Other', 'Others') AND
      lead_class IN ('Other', 'Others')
ORDER BY row_id;

You can self join table limiting initial row to not others, and joind to other and them to be sequential:

select t.row_id, t.class, th.row_id prev_row_id , th.class prev_class
  from my_table t
  join my_table th 
    on (th.row_id = t.row_id + 1 and th.class in ('Other', 'Others'))
 where t.class not in ('Other', 'Others')

*Caution: this will work only if id is continuous

You can use window functions. For example lag:

-- sample data
with dataset(row_id, class) as(
    values (1,  'Math'),
        (2,  'Math'),
        (6,  'Math'),
        (7,  'Other'),
        (9,  'Other'),
        (10, 'Biology'),
        (11, 'Biology'),
        (12, 'Other'),
        (13, 'Other'),
        (14, 'Biology'),
        (15, 'Biology'),
        (16, 'Others'),
        (17, 'Others'),
        (18, 'Others'),
        (19, 'Physics'),
        (20, 'Others')
),

-- query parts
with_prev as(
 SELECT *,
    lag(row_id) OVER w AS prev_row_id,
    lag(class) OVER w AS prev_class
 FROM dataset
 WINDOW w AS (ORDER BY row_id) -- Trino allows sharing window
)

select *
from with_prev
where prev_class not like 'Other%' and class like 'Other%'; -- or in ('Other', 'Others') 

Output: