1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for passionate videogamers on all platforms

-- Created by aminography [https://stackoverflow.com/users/1631967/aminography]

/* Note that in some cases the tag reputation exceeds the total reputation of 
   a user (e.g. CommonsWare in android tag). In these cases, they have offered
   bounties or subjected to the daily reputation limitation of 200. 
   (https://stackoverflow.com/help/whats-reputation) */

SELECT TOP ##Top:int?1000##
    ROW_NUMBER() OVER(
        ORDER BY SUM(
            CASE
                WHEN Votes.VoteTypeId = 1 THEN 15                  -- AcceptedByOriginator
                WHEN Votes.VoteTypeId = 2 THEN 10                  -- UpMod
                WHEN Votes.VoteTypeId = 3 THEN -2                  -- DownMod
                WHEN Votes.VoteTypeId = 9 THEN Votes.BountyAmount  -- BountyClose
                ELSE 0                                             -- Other
            END
        )
    DESC) AS [Rank],
    Users.Id AS [User Link],
    SUM(
        CASE
            WHEN Votes.VoteTypeId = 1 THEN 15                  -- AcceptedByOriginator
            WHEN Votes.VoteTypeId = 2 THEN 10                  -- UpMod
            WHEN Votes.VoteTypeId = 3 THEN -2                  -- DownMod
            WHEN Votes.VoteTypeId = 9 THEN Votes.BountyAmount  -- BountyClose
            ELSE 0                                             -- Other
        END
    ) AS [##Tag?android## Reputation],
    Users.Reputation AS [Total Reputation],
    COUNT(DISTINCT Posts.Id) AS [##Tag?android## Answers],
    SUM(
        CASE
            WHEN Votes.VoteTypeId = 9 THEN Votes.BountyAmount  -- BountyClose
            ELSE 0                                             -- Other
        END
    ) AS [💰 Bounties],
    SUM(
        CASE
            WHEN Votes.VoteTypeId = 2 THEN 1                   -- UpMod
            ELSE 0                                             -- Other
        END
    ) AS [👍 Votes],
    SUM(
        CASE
            WHEN Votes.VoteTypeId = 3 THEN -1                  -- DownMod
            ELSE 0                                             -- Other
        END
    ) AS [👎 Votes],
    SUM(
        CASE
            WHEN Votes.VoteTypeId = 1 THEN 1                   -- AcceptedByOriginator
            ELSE 0                                             -- Other
        END
    ) AS [✔️ Accepted]
    
FROM Users 
    INNER JOIN Posts ON Users.Id = Posts.OwnerUserId
    INNER JOIN PostTags ON PostTags.PostId = Posts.ParentId
    LEFT JOIN Votes ON Votes.PostId = Posts.Id
    INNER JOIN Tags ON Tags.Id = PostTags.TagId

WHERE Posts.PostTypeId = 2 AND            -- Answer
    Posts.CommunityOwnedDate IS NULL AND  -- Non-Community
    Tags.TagName = '##Tag##'              -- Tag Name

GROUP BY Users.Id, Users.Reputation

ORDER BY CASE Users.Id WHEN ##TargetUserId:int?0## THEN 1 ELSE 0 END DESC,
    [##Tag## Reputation] DESC

fork query

edited jan 24

created jun 6 20 aminography