[Challenge] 計算 T-SQL 挑戰贏家的 SQL Stars 點數

Beyond Relational TSQL Challenge 38

This challenge involves parsing a delimited string and applying some logic to perform some calculations based on the position of tokens within the string. This challenge has got a fake (or self?) reference to the TSQL Challenges itself. Your job is to calculate the SQL Stars awarded to each of the winners by parsing a delimited string.

• Sample Data

Challenge         Winners
----------------- ---------------------------------------------------
TSQL Challenge 25 leszek,lmu92/xaloc,,karinloos,Erick,,,dishdy,jobacr
TSQL Challenge 24 ,Dalibor,,lwkwok,,,,lmu92,dishdy
TSQL Challenge 23 Mark,Dalibor,Beakdan,,Parth,Ramireddy,lmu92,Ruby

Note that the data given above is for representational purpose only and it may not match with the actual SQL Stars awarded to the people mentioned in the sample data.

• Expected Results

Winner    SQL Stars
--------- ---------
Dalibor        18
lmu92          16
leszek         10
Mark           10
xaloc           9
Beakdan         8
karinloos       7
lwkwok          7
Erick           6
Parth           6
dishdy          5
Ramireddy       5
Ruby            3
jobacr          2

• Rules
1. Each winner gets 1 to 10 SQL Stars. The highest is 10 and lowest is 1.
2.  The name on the left most position will get 10 SQL Stars and the next gets 9 and so on. In the third row Mark gets 10 SQL Stars and Dalibor gets 9 etc.
3. You should process only people who come in the top 10 positions. The input column may have more names, but they should be ignored.
4. There may be cases where more than one user is awarded the same number of stars. In such a case, those usernames will be separated by a “/”. In the first example, “lmu92” and “xaloc” are given 9 SQL Stars each.
5. It is quite possible that there is no one to claim the SQL Stars at a given position. In the second row, no one claims 10 SQL Stars. Dalibor gets 9 stars, no one to claim 8th position and lwkwok gets 7 SQL Stars etc.
6. Output should be ordered by the number of SQL Stars each user gets. People with highest number of SQL Stars should come on top. If more than one user gets the same number of total stars, secondary order should be done by username (case insensitive).
    

• 個人解法

IF OBJECT_ID('TC38','U') IS NOT NULL 
  DROP TABLE TC38
GO

CREATE TABLE TC38 
(
  Challenge VARCHAR(20),
  Winners VARCHAR(MAX)
)
GO

INSERT INTO TC38 (Challenge, Winners)
SELECT 'TSQL Challenge 25', 'leszek,lmu92/xaloc,,karinloos,Erick,,,dishdy,jobacr'
UNION ALL
SELECT 'TSQL Challenge 24', ',Dalibor,,lwkwok,,,,lmu92,dishdy'
UNION ALL
SELECT 'TSQL Challenge 23', 'Mark,Dalibor,Beakdan,,Parth,Ramireddy,lmu92,Ruby'

;
WITH CTE AS
(
  SELECT Winners , 10 AS SeqNO , 1 AS StartPos , CAST(CHARINDEX(',',Winners) AS smallint) AS EndPos
  FROM TC38
  UNION ALL
  SELECT Winners, SeqNO - 1 , EndPos + 1 , CAST(CHARINDEX(',',Winners,EndPos + 1) AS smallint)
  FROM CTE
  WHERE EndPos > 0
)
, CTE2 AS
(
  SELECT 
    SUBSTRING
      (
        Winners,
        StartPos,
        COALESCE(NULLIF(EndPos,0),LEN(Winners)+1) - StartPos
      ) as DataString , SeqNO
  FROM CTE
)
,CTE3 AS
(
  SELECT DataString , SeqNO ,1 AS StartPos , CAST(CHARINDEX('/',DataString) AS smallint) AS EndPos 
  FROM CTE2
  UNION ALL
  SELECT DataString , SeqNO ,EndPos + 1 , CAST(CHARINDEX('/',DataString,EndPos + 1) AS smallint) 
  FROM CTE3
  WHERE EndPos > 0
)
SELECT DataString , SeqNO  * COUNT(*) AS [SQL Starts]
FROM
  (
    SELECT 
      SUBSTRING
        (
          DataString ,
          StartPos ,
          COALESCE(NULLIF(EndPos,0),LEN(DataString)+1) - StartPos
        ) as DataString , SeqNO
    FROM CTE3
    WHERE DataString <> ''
  ) AS T
GROUP BY DataString , SeqNO
ORDER BY [SQL Starts] DESC

CTE 跑兩次，分別針對 "," 和 "/" 去處理即可

參考資料：
• TSQL Challenge 38 - Calculate SQL Stars awarded to the Winners of TSQL Challeng