0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and amateur woodworkers

WITH zUsersBadges AS (
    SELECT  b.Id
            , b.UserId
            , b.Name
            , b.Date
            , b.Class
            , [Badge Class] = (
                CASE    WHEN class = 1 THEN 'Gold'
                        WHEN class = 2 THEN 'Silver'
                        WHEN class = 3 THEN 'Bronze'
                END
            )
            , [Is tag badge] = IIF (b.TagBased = 1, 'Yes', 'No')
    FROM    Badges b
    WHERE   b.UserId = ##UserId:INT##
)
SELECT      ub.Name                 AS [Badge Name]
            , ub.[Badge Class]
            , ub.[Is tag badge]
            , ub.Date               AS [Date Earned]
            , [In Top N of earners] = (
                SELECT  COUNT (ob.ID)
                FROM    Badges ob
                WHERE   (ob.Name = ub.Name  AND  ob.Class = ub.Class  AND  ob.Id <= Ub.Id)  -- Faster but may give slightly higher rank
                --WHERE   (ob.Name = ub.Name  AND  ob.Class = ub.Class  AND  ob.Date <= Ub.Date)  -- Slower, but gives exact rank.
            )
FROM        zUsersBadges ub

view current version

edited jun 20 19

created jun 20 19 Brock Adams