Database Challenges

-- jesse_reich_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 n AS (
  SELECT TOP 40 ROW_NUMBER() OVER (ORDER BY [name]) AS Number
  FROM sys.all_objects
)
,chars AS ( 
SELECT
  t.Data,
  SUBSTRING(t.Data,n.Number,1) AS [Char],
  n.Number AS Pos
FROM n n
INNER JOIN @t t ON n.Number <= len(t.Data)
) 
,groups AS (
SELECT
  Data,
  [Char],
  Pos,
  Pos - ROW_NUMBER() OVER (ORDER BY Data,[Char],Pos) AS CharGroup
FROM chars
)
,final AS (
SELECT 
  Data,
  [Char],
  MIN(Pos) AS Pos,
  COUNT(*) AS [Len]
FROM groups g
GROUP BY 
  Data,
  [Char],
  CharGroup
HAVING COUNT(*) > 1
) 
SELECT
  f.Data,
  f.[Char],
  f.Pos,
  f.[Len]
FROM final f
JOIN (
  SELECT 
    Data,
    MAX([Len]) AS MaxLen
  FROM final
  GROUP BY Data
  ) d
ON f.Data = d.Data
ORDER BY 
  d.MaxLen DESC,
  f.Data,
  f.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.