sqlite> select t.*,
               row_number() over (order by n),
               row_number() over (partition by c order by n),
               row_number() over (order by n) - row_number() over (partition by c order by n)
          from t;
1|a|1|1|0
2|a|2|2|0
3|a|3|3|0
4|b|4|1|3
5|b|5|2|3
6|b|6|3|3
7|a|7|4|3
8|a|8|5|3