SET NOCOUNT ON;
DECLARE @tmp TABLE (
  ID      INT NOT NULL PRIMARY KEY
, ITEM_ID INT NOT NULL
)

DECLARE @HIST TABLE (
  ID      INT NOT NULL PRIMARY KEY
, ITEM_ID INT NOT NULL
, CHC     INT NOT NULL -- consecutive hit count
)
INSERT INTO @tmp 
        (ID, ITEM_ID )
VALUES  
    (1, 1)
  , (2, 1)
  , (3, 1)
--, (4, 1)
  , (5, 1)
--, (6, 1)
  , (7, 1)
  , (8, 1)
--, (9, 1)
  ,(10, 1)

--,(11, 2)
  ,(12, 2)
  ,(13, 2)
--,(14, 2)
--,(15, 2)
--,(16, 2)
  ,(17, 2)
  ,(18, 2)
--,(19, 2)
  ,(20, 2)

	DECLARE @ID         AS INT 
	DECLARE @prev_ID	AS INT

	DECLARE POPULATE_HIST CURSOR FAST_FORWARD FOR

	SELECT  ID, ID - 1 AS previous_id			
	FROM @tmp
	ORDER BY ID
			
	OPEN POPULATE_HIST

	FETCH NEXT FROM POPULATE_HIST INTO @ID , @PREV_ID

	WHILE (@@FETCH_STATUS = 0)
		BEGIN
			INSERT INTO @HIST (ID, ITEM_ID, CHC)
			SELECT	  curr.ID
					, curr.ITEM_ID					
					, CASE WHEN prev.CHC IS NULL THEN 0 						  
					       ELSE prev.CHC + 1     END
					  as CHC	
				FROM @tmp AS curr
			LEFT OUTER JOIN 
				(	SELECT 
				          ID
				        , ITEM_ID
						, CHC
					FROM @HIST
					WHERE ID = @PREV_ID
					
				) AS prev
			ON curr.ITEM_ID = prev.ITEM_ID					
			WHERE curr.ID = @ID	
			GROUP BY
					  curr.ID
					, curr.ITEM_ID					
					, prev.CHC
					
		FETCH NEXT FROM POPULATE_HIST INTO @ID ,@PREV_ID
		END										
	CLOSE POPULATE_HIST
	DEALLOCATE POPULATE_HIST

SELECT * FROM @HIST	
/*
ID	ITEM_ID	CHC
1	1	0
2	1	1
3	1	2
5	1	0
7	1	0
8	1	1
10	1	0
12	2	0
13	2	1
17	2	0
18	2	1
20	2	0
*/

;WITH cte(id,item_id,seq) AS (
	SELECT id, item_id, ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY ID)-id seq FROM @tmp)
SELECT id, item_id, ROW_NUMBER() OVER (PARTITION BY item_id, seq ORDER BY id)-1 FROM cte ORDER BY id

SELECT id, item_id, ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY ID) row_num,
ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY ID)-id seq
FROM @tmp