Database Challenges

-- syed_mehroz_alam_tsqlchallenge14-numbertable.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'

--split to characters
;With Splitted
as
(
	select n.number as iterationNo, substring(tbl.data, n.number, 1) as ithCharacter, tbl.Data
		,RN = n.number - ROW_NUMBER() over (PARTITION by Data,substring(tbl.data, n.number, 1) order by n.number)
	from @t tbl
	cross join master..spt_values n
	where n.Type = 'P' and n.Number between 1 and 36
)
--The final result. however, we need to sort it as the desired output
,Result
as
(
	Select Data
		,Min(ithCharacter) as Char
		,Min(iterationNo) as Pos
		,COUNT(*) as Len
	from Splitted
	group by Data, RN, ithCharacter
	having COUNT(*) > 1
)
--result grouped by data to assist in final sorting
,Grouped
as
(
	Select Data, MAX(Len) as MaxLength, COUNT(*) as GroupCount
	from Result 
	group by Data
)

--output
Select 
	R.Data, R.Char, R.Pos, R.Len
from Result R
	join Grouped G on R.Data=G.Data
order by G.MaxLength desc, G.GroupCount desc, R.Data, R.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.