Database Challenges

-- Jakob_Eskar_tsqlchallenge_14.sql

DECLARE @t TABLE (Data VARCHAR(40) ) 
 
INSERT @t (Data) SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97' 
INSERT @t (Data) SELECT '0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1' 
INSERT @t (Data) SELECT '4A73E7EB-7777-4A04-9258-F1E75097977C' 
INSERT @t (Data) SELECT '5AAF477C-274D-400D-9067-035968F33B19' 
INSERT @t (Data) SELECT '725DA718-30D0-44A9-B36A-89F27CDFEEDE' 
INSERT @t (Data) SELECT '8083ED5A-D3B9-4694-BB04-F0B09C588888' 
 
;WITH Numbers AS
( -- Create char indexes
	SELECT [n] = 1
	UNION ALL 
	SELECT [n] + 1
	  FROM [Numbers]
	 WHERE [n] < 36
), CharPos AS
( -- Find each Char and it's index	
	SELECT [Data] = [Data]
		  ,[Char] = SUBSTRING([Data], n.n, 1)
		  ,[Pos]  = n.n
	  FROM @t 
		CROSS JOIN Numbers n
), Sequences AS
(
	-- Find and Calculate each sequence and it's length
	SELECT [Data] = [d].[Data] 
		  ,[Char] = [d].[Char]
		  ,[Pos]  = MIN([d].[Pos])
		  ,[Len]  = MAX([d].[Pos]) - MIN([d].[Pos]) + 1
	  FROM (SELECT [Data]
				  ,[Char]
				  ,[Pos]
				  ,[Pos] - ROW_NUMBER() OVER(ORDER BY [Data], [Char], [Pos]) AS Grp
			  FROM [CharPos]) AS [d]
	 GROUP BY [d].[Data], [d].[Char], [grp]
	 HAVING MIN([d].[pos]) != MAX([d].[Pos])
), DataSequenceMaxLength AS
(
    -- For each data find the longest sequence
	SELECT [Data], [AccLen] = MAX(Len)
	  FROM [Sequences]
	 GROUP BY [Data]
)
-- Show the result
SELECT [s].[Data], [s].[Char], [s].[Pos], [s].[Len]
  FROM [Sequences] [s]
    INNER JOIN [DataSequenceMaxLength] [sl] ON [s].[Data] = [sl].[Data]
 ORDER BY [AccLen] DESC, [Data], [Pos];

Did you understand how this solution work? If you find it difficult to understand, you can Request an Explanation or you can Write an explanation to help others better understand this.