select
						i
						, c
						, g
						, gi
						, sum(1) over (partition by g) gn
				from (
				select
						i
						, c
						, g
						, row_number() over(partition by g order by i) gi
				from (
				select 
						i
						, c
						, sum(case c when 'a' then 1 else 0 end) over (order by i) g
				from (
						select 1 i, 'a' c union all
						select 2, 'b' union all
						select 3, 'c' union all
						select 4, 'a' union all
						select 5, 'b' union all
						select 6, 'a' union all
						select 7, 'b' union all
						select 8, 'c' union all
						select 9, 'd' 
				) t
				) t
				) t
				order by i