Score by Tag

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

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

Declare @UpvoteQ int = 5;
Declare @UpvoteA int = 10;
Declare @Downvote int = -2;
Declare @AccAns int = 15;
DECLARE @UserId int = ##UserId?47188##;

Select TagName, Rank, (UpRep + AccAns) as UpRep, DownRep as BadRep
From
  (
  Select 
    TagName, 
    Sum(Case when (VoteTypeId = 2 and Posts.PostTypeId = 2) then @UpvoteA 
        when (VoteTypeId = 2 and Posts.PostTypeId = 1) then @UpvoteQ
        else 0 end) AS UpRep,
    Sum(Case when VoteTypeId = 3 then @Downvote else 0 end) AS DownRep,
    Sum(Case when acc.Score > 0 then @AccAns else 0 end) as AccAns,
    Row_Number() over(order by Sum(Posts.Score) desc) as Rank
  From 
    Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.id
    INNER JOIN Posts ON (Posts.ParentId = PostTags.PostId or Posts.Id = PostTags.PostId)
    Inner JOIN Votes ON Votes.PostId = Posts.Id
    Left Join Posts acc on Posts.Id = acc.AcceptedAnswerId
    --Where Posts.OwnerUserId = @UserId
  GROUP BY TagName
  ) as Stats
Order by Rank asc

Enter Parameters

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