Find transitions from exact values SQL

Question

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.

Answer 1

We can use the LEAD() analytic function here:

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;