Order of user being awarded each of their tag badges

1

Please login or register to vote for this query.

(click on this box to dismiss)

This query retrieves the order in which the specified user was awarded each of their respective tag badges compared to other users, and the total number of users that currently have the badge.

Stack Overflow

Q&A for programmers

WITH CTE_BadgesReceived AS
(
    SELECT b.Name   
         , b.Class      
         , MIN (b.Date) AS Date
         , MIN (b.Id)   AS Id
    FROM Badges b
    WHERE b.UserId = ##UserId##
      AND b.TagBased = 1
    GROUP BY b.Name, b.Class
)

SELECT b.Name AS [Badge Name]
     , CAST (b.Class AS CHAR(1)) + N' – ' +
           CASE b.Class WHEN 1 THEN 'Gold'
                        WHEN 2 THEN 'Silver'
                        WHEN 3 THEN 'Bronze'
                        END AS [Badge Class]
     , b.Date AS [Date Received]

     , [Award Order] =
       (
           SELECT COUNT (*)
           FROM Badges b2
           WHERE b2.Name  = b.Name
             AND b2.Class = b.Class
             AND ( b2.Date < b.Date OR
                   b2.Date = b.Date AND b2.Id <= b.Id )
       )
       
--   , [Award Order Link] = 
--        'https://data.stackexchange.com/stackoverflow/query/453070' +
--        '?BadgeId=' + CAST (b.Id AS VARCHAR(10)) + 
--        '&UserId=' + CAST (##UserId## AS VARCHAR(10)) +
--        '|Link'
              
     , [Total Awards] =
       (
           SELECT COUNT (*)
           FROM Badges b2
           WHERE b2.Name  = b.Name
             AND b2.Class = b.Class
       )       
       
FROM CTE_BadgesReceived b
ORDER BY [Award Order]

Enter Parameters

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