1

Please login or register to vote for this query.

(click on this box to dismiss)

Return tag scores for active, high-rep users. RepLimit: lower limit for consideration as high-rep. ActiveWithinXDays: only consider users active within the last (this many) days. DaysHistoryToUse: limit consideration to answers from the last (this many) days.

Q&A about the site for committed Christians, experts in Christianity and those interested in learning more

WITH ActiveUsers as 
(SELECT Id, DisplayName, Reputation, LastAccessDate 
FROM Users
WHERE Reputation >= ##RepLimit:int## AND 
DATEDIFF(day, LastAccessDate, GetDate()) <= ##ActiveWithinXDays:int## 
),
Answers AS
(SELECT ActiveUsers.Id AS UserId, ActiveUsers.DisplayName, Posts.Id AS AnswerId, Posts.ParentId AS QuestionId, Posts.Score, Tags
FROM Posts
JOIN ActiveUsers ON ActiveUsers.Id = Posts.OwnerUserId
JOIN PostTypes ON PostTypes.Id = Posts.PostTypeId
WHERE PostTypes.Name = 'Answer' AND
DATEDIFF(day, Posts.CreationDate, GetDate()) <= ##DaysHistoryToUse:int##
)

SELECT PostTags.TagId, Tags.TagName, Answers.UserId, Answers.DisplayName, Sum(Answers.Score) AS TagScore
FROM PostTags
JOIN Tags ON PostTags.TagId = Tags.Id
Join Answers ON PostTags.PostId = Answers.QuestionId
GROUP BY PostTags.TagId, Tags.TagName, Answers.UserId, Answers.DisplayName
ORDER BY UserId, TagScore DESC, TagId

view current version

edited mar 27 14

created mar 25 14 Nicholas R. Peterson