Characters per Score (CpS) by user on answers +

0

Please login or register to vote for this query.

(click on this box to dismiss)

How much net score and views does each character in your answers among various tags bring you? Find out with this query ;)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @Items TABLE (ParentId int, Score int, Views int, Length int, Tags nvarchar(150))

INSERT INTO @Items (ParentId, Score, Views, Length, Tags)
  SELECT i.ParentId,
    i.Score,
    q.ViewCount,
    LEN(i.Body),
    q.Tags
  FROM Posts i
    INNER JOIN Posts q
      ON i.ParentId = q.Id
  WHERE i.OwnerUserId = ##UserId:int##
    AND i.PostTypeId = 2
      
DECLARE @Tags TABLE (Tag nvarchar(30), TScore int, TViews int, TLength int)

INSERT INTO @Tags (TScore, TViews, TLength)
  SELECT SUM(Score),
    SUM(Views),
    SUM(Length)
  FROM @Items

SELECT t.TagId AS TagId
INTO #TagList
FROM @Items i
  INNER JOIN PostTags t
    ON i.ParentId = t.PostId
GROUP BY t.TagId

DECLARE @Tag varchar(30);
  
WHILE EXISTS (SELECT * FROM #TagList)
BEGIN
  SET @Tag = (SELECT TOP 1 TagId
              FROM #TagList
              ORDER BY TagId);
  
  INSERT INTO @Tags (Tag, TScore, TViews, TLength)
    SELECT t.TagName,
      SUM(i.Score),
      SUM(i.Views),
      SUM(i.Length)
    FROM @Items i
      INNER JOIN PostTags pt
        ON i.ParentId = pt.PostId
      INNER JOIN Tags t
        ON pt.TagId = t.Id
    WHERE pt.TagId = @Tag
    GROUP BY t.TagName
    
  DELETE #TagList
  WHERE TagId = @Tag
END

SELECT Tag AS [TagName],
  TScore AS Score,
  TViews AS Views,
  TLength AS Length,
  CASE
    WHEN TScore <> 0
    THEN CASE
      WHEN Tag IS NOT NULL
      THEN 'http://stackoverflow.com/search?tab=votes&q=user:' +
        ##UserId:string## + '+[' + Tag + ']|' + CAST(TLength / TScore AS varchar)
      ELSE CAST(TLength / TScore AS varchar)
    END ELSE 'Undefined.'
  END AS CpS,
  CASE
    WHEN TScore <> 0
    THEN CASE
      WHEN Tag IS NOT NULL
      THEN 'http://stackoverflow.com/search?tab=active&q=user:' +
        ##UserId:string## + '+[' + Tag + ']|' + CAST(TViews / TScore AS varchar)
      ELSE CAST(TViews / TScore AS varchar)
    END ELSE 'Undefined.'
  END AS VpS
FROM @Tags
ORDER BY Tag

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results