group rows by pilot row, show line numbers and line count

art.sql

				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