User's "Top N" rank for each of his/her badges


Please login or register to vote for this query.

(click on this box to dismiss)

Raspberry Pi

Q&A for users and developers of hardware and software for Raspberry Pi

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'
            , [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
ORDER BY    ub.Name, ub.Date

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results