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

1

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

Q&A for Ubuntu users and developers

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

Enter Parameters

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