Users by their total score on their most-answered tags

2

Please login or register to vote for this query.

(click on this box to dismiss)

Returns the chosen site's users (above a threshold reputation) with their top tags listed by (a) the total number of non-wiki answers they have posted in that tag, and (b) the total score garnered by those answers.

Physics

Q&A for active researchers, academics and students of physics

--site name as per query 516017 for SEDE URLs
DECLARE @siteinit VARCHAR(100), @site VARCHAR(100)
SET @siteinit=REPLACE(LOWER( (SELECT DB_NAME() AS [Current Database]) ),'StackExchange.','')
SET @site = REPLACE(REPLACE(REPLACE( --REPLACE(
CASE
  WHEN @siteinit NOT LIKE '%.Meta' THEN @siteinit
  ELSE 'meta.'+REPLACE(@siteinit,'.Meta','')
  END
  ,'audio','avp') ,'stackoverflow.br','stackoverflow.pt') ,'stackoverflow.','')
  --,'meta.','')+'me' -- Some meta sites require this
  
  
  
SELECT
userid AS [User Link],
reputation,
MAX(total1) AS 'total1', MAX(score1) AS 'score1',
MAX(total2) AS 'total2', MAX(score2) AS 'score2',
MAX(total3) AS 'total3', MAX(score3) AS 'score3',
MAX(total4) AS 'total4', MAX(score4) AS 'score4',
MAX(total5) AS 'total5', MAX(score5) AS 'score5',
'http://data.stackexchange.com/'+@site+'/query/840693?UserId='+
  CAST(userid AS varchar)+'#graph|Link' AS 'Distribution'
FROM (
  SELECT --TOP 5
    MAX(users.Id) AS 'userid',
    MAX(users.Reputation) AS 'reputation',
    ROW_NUMBER() OVER (PARTITION BY answers.OwnerUserId 
        ORDER BY COUNT(answers.Id) DESC)  AS 'Rank',
    ('total'+CAST(ROW_NUMBER() OVER (PARTITION BY answers.OwnerUserId 
        ORDER BY COUNT(answers.Id) DESC) AS varchar)) AS 'NumberRank',
    SUM(answers.Score) AS TagScore,
    ('score'+CAST(ROW_NUMBER() OVER (PARTITION BY answers.OwnerUserId 
        ORDER BY COUNT(answers.Id) DESC) AS varchar)) AS 'ScoreRank',
    COUNT(answers.Id) AS TotalAnswers
  FROM Tags
  INNER JOIN Posts questions ON questions.Tags LIKE '%<'+Tags.TagName+'>%'
  INNER JOIN Posts answers ON answers.ParentId = Questions.Id
  INNER JOIN Users users ON users.Id = answers.OwnerUserId
  WHERE questions.CommunityOwnedDate IS NULL
    AND users.Reputation > ##MinReputation?1000##
  GROUP BY Tags.TagName, answers.OwnerUserId
) TopRankedTable
PIVOT(
  MAX(TotalAnswers) 
  FOR NumberRank IN ("total1","total2","total3","total4","total5")
) AS PivotTable1
PIVOT(
  MAX(TagScore) 
  FOR ScoreRank IN ("score1","score2","score3","score4","score5")
) AS PivotTable2
WHERE Rank <=5
GROUP BY userid, reputation
ORDER BY score1, score2, score3, score4, score5

Enter Parameters

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