0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for enthusiasts and power users of the Android operating system

-- UserId: User ID
DECLARE @userId INT = ##UserId:INT##
-- MinPost: Min number of posts
DECLARE @minPost INT = ##MinPost:INT?5##
-- TopTags: Top n Tags
DECLARE @topTags INT = ##TopTags:INT?10##

SELECT TOP (@topTags)
  Tags.tagName,
  SUM(CASE WHEN Posts.PostTypeId = 1 THEN Posts.Score ELSE 0 END)
    AS [Total Question Score],
  SUM(CASE WHEN Posts.PostTypeId = 1 THEN 1 ELSE 0 END)
    AS [Question Posts],
  SUM(CASE WHEN Posts.PostTypeId = 2 THEN Posts.Score ELSE 0 END)
    AS [Total Answer Score],
  SUM(CASE WHEN Posts.PostTypeId = 2 THEN 1 ELSE 0 END)
    AS [Answer Posts],
  SUM(Posts.Score) AS [Total Score],
  COUNT(*) AS [Post Count],
  ROUND(AVG(CAST(Posts.Score AS FLOAT)), 1, 1) AS [Average Score]
FROM Posts
JOIN PostTags
  ON Posts.Id = PostTags.PostId OR Posts.ParentId = PostTags.PostId
JOIN Tags
  ON PostTags.TagId = Tags.Id
WHERE Posts.OwnerUserId = @userId
GROUP BY Tags.tagName
HAVING COUNT(*) >= @minPost
ORDER BY [Average Score] DESC

fork query

edited oct 26 20

created oct 26 20 NearHuscarl