Score and posts per tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

WITH Answers AS (SELECT t.TagName, SUM(a.Score) AS Score, COUNT(a.Id) AS Posts
  FROM Posts AS a
  INNER JOIN Posts AS q
    ON a.ParentId = q.Id
  INNER JOIN PostTags AS pt
    ON q.Id = pt.PostId
  INNER JOIN Tags AS t
    ON t.Id = pt.TagId
  WHERE a.OwnerUserId = ##UserId:int##
    AND a.CommunityOwnedDate IS NULL -- exclude Community Wikis
  GROUP BY t.TagName),
Questions AS (SELECT t.TagName, COUNT(q.Id) AS Posts
  FROM Posts AS q
  INNER JOIN PostTags AS pt
    ON q.Id = pt.PostId
  INNER JOIN Tags AS t
    ON t.Id = pt.TagId
  WHERE q.OwnerUserId = ##UserId:int##
    AND q.CommunityOwnedDate IS NULL -- exclude Community Wikis
  GROUP BY t.TagName)
SELECT Answers.TagName, Answers.Score,
  COALESCE(Answers.Posts, 0) + COALESCE(Questions.Posts, 0) AS Posts
  FROM Answers
  FULL OUTER JOIN Questions
    ON Answers.TagName = Questions.TagName
  ORDER BY Answers.Score DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results