Leaderboard of users top badge for a specific tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping

Q&A for gardeners and landscapers

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

WITH Board AS (
    SELECT TOP ##Top:int?1000##
        Badges.Id AS BadgeId,
        Users.Id AS [User Link],
        Reputation,
        Badges.Class,
        (CASE
             WHEN Class = 1 THEN Name + N'🥇'       
             WHEN Class = 2 THEN Name + N'🥈'
             WHEN Class = 3 THEN Name + N'🥉' 
             ELSE ''                              
         END) as Badge, 
        Date
    
    FROM Users
        INNER JOIN Badges ON Users.Id = Badges.UserId

    WHERE TagBased = 1 AND
        LOWER(Location) LIKE LOWER('%##Location?##%') AND
        LOWER(Name) LIKE LOWER('##Tag?android##')
    
    ORDER BY Badges.Class, Badges.Date ASC
)

SELECT 
    ROW_NUMBER() OVER (ORDER BY Board.Class) AS [#], 
    Board.[User Link],  
    Board.Badge,
    Board.Date AS [Earned on],
    Board.Reputation
    
FROM (SELECT 
         Board.[User Link], 
         MAX (Board.BadgeId) as BadgeId FROM Board GROUP BY Board.[User Link]
     ) TopUserBadges LEFT JOIN Board ON Board.BadgeId = TopUserBadges.BadgeId
    
ORDER BY Board.Class, Board.Date ASC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results