Add running count of specific state changes I have a table in PostgreSQL with below fields, where timestamp is simplified as integers:

ts  status
1     m
2     m
3     i
4     s
5     s
6     i
7     i
8     m
9     s
10    m


I want to break then aggregate rows based on the presence of the 's' status, to spit out an additional column which I can use to easily group rows:

ts  status  flag
1     m      1
2     m      1
3     i      1
4     s      1
5     s      1
6     i      2
7     i      2
8     m      2
9     s      2
10    m      3


The dynamic column flag shall increment whenever status changes from 's' to something else.

I don't see a clear pattern to partition by. How to do this?
Two levels of window functions.
To keep the code short, you can combine the aggregate FILTER clause with the outer window function:

SELECT ts, status
     , 1 + count(*) FILTER (WHERE previous_status = 's' AND status &lt;&gt; 's')
                    OVER (ORDER BY ts) AS flag
FROM (
   SELECT *, lag(status) OVER (ORDER BY ts) AS previous_status
   FROM   tbl
   ) sub;


db&lt;>fiddle here

Assuming current Postgres and all columns NOT NULL.
Related:


Return counts for multiple ranges in a single SELECT statement
Grouping or Window