Leaderboard - Answer Badges

0

Please login or register to vote for this query.

(click on this box to dismiss)

Leaderboard of answer badges. A 'Weight' statistic is computed where the rarest awarded badges contributes proportionally greater than less awarded ones. The 'Weight' statistic is given in % where the sum of weights from all users is equal 100%: Weight (%) = {Avg[(user_count_of_enlightened/total_awarded_enlightened), (user_count_of_explainer/total_awarded_explainer), (...), (user_count_of_unsung_hero/total_awarded_unsung_hero)] * 100}. The query is also useful (by sorting its table's columns results) to i) visualize top users in answer tags which are awarded multiple times, and ii) visualize users who earned badges which are awarded only once. Tag badges are presented summarized by their classes: bronze, silver and gold. Any error 'division by zero' found is because one of the badges has not been awarded yet in site (Illuminator and Reversal are not caught by this error; will fix for other badges asap)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Leaderboard of answer badges
-- [teacher] badge was not included because it is too trivial

DECLARE @total_enlightened INT
DECLARE @total_explainer INT
DECLARE @total_refiner INT
DECLARE @total_illuminator INT
DECLARE @total_generalist INT
DECLARE @total_guru INT
DECLARE @total_nice_answer INT
DECLARE @total_good_answer INT
DECLARE @total_great_answer INT
DECLARE @total_populist INT
DECLARE @total_reversal INT
DECLARE @total_revival INT
DECLARE @total_necromancer INT
DECLARE @total_self_learner INT
DECLARE @total_tenacious INT
DECLARE @total_unsung_hero INT
DECLARE @total_bronze_tag INT
DECLARE @total_silver_tag INT
DECLARE @total_gold_tag INT

SET @total_enlightened = (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Enlightened')
SET @total_explainer = (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Explainer')
SET @total_refiner = (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Refiner')
SET @total_illuminator = (SELECT
                       CASE WHEN COUNT(1) > 0 
                            THEN 
                              (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Illuminator')
                            ELSE 1
                            END
                       FROM Badges WHERE Badges.Name = 'Illuminator'
                       ) -- badge with few awards; if not awarded assume value equal 1 to avoid division by zero   
SET @total_generalist = (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Generalist')
SET @total_guru = (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Guru')
SET @total_nice_answer = (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Nice Answer')
SET @total_good_answer = (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Good Answer')
SET @total_great_answer = (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Great Answer')
SET @total_populist = (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Populist')
SET @total_reversal = (SELECT
                       CASE WHEN COUNT(1) > 0 
                            THEN 
                              (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Reversal')
                            ELSE 1
                            END
                       FROM Badges WHERE Badges.Name = 'Reversal'
                       ) -- badge with few awards; if not awarded assume value equal 1 to avoid division by zero                         
SET @total_revival = (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Revival')
SET @total_necromancer = (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Necromancer')
SET @total_self_learner = (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Self-Learner')
SET @total_tenacious = (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Tenacious')
SET @total_unsung_hero = (SELECT count(Id) FROM Badges WHERE Badges.Name = 'Unsung Hero')
SET @total_bronze_tag = (SELECT count(Id) FROM Badges WHERE Badges.TagBased = 'true' AND Badges.Class = 3)
SET @total_silver_tag = (SELECT count(Id) FROM Badges WHERE Badges.TagBased = 'true' AND Badges.Class = 2)
SET @total_gold_tag = (SELECT count(Id) FROM Badges WHERE Badges.TagBased = 'true' AND Badges.Class = 1);

WITH
enlightened AS 
(
SELECT UserId, count(UserId) AS badges
FROM Badges
WHERE Badges.Name = 'Enlightened'
GROUP BY UserId
),
explainer AS 
(
SELECT UserId, count(UserId) AS badges
FROM Badges
WHERE Badges.Name = 'Explainer'
GROUP BY UserId
),
refiner AS 
(
SELECT UserId, count(UserId) AS badges
FROM Badges
WHERE Badges.Name = 'Refiner'
GROUP BY UserId
),
illuminator AS 
(
SELECT UserId, count(UserId) AS badges
FROM Badges
WHERE Badges.Name = 'Illuminator'
GROUP BY UserId
),
generalist AS 
(
SELECT UserId, count(UserId) AS badges
FROM Badges
WHERE Badges.Name = 'Generalist'
GROUP BY UserId
),
guru AS 
(
SELECT UserId, count(UserId) AS badges
FROM Badges
WHERE Badges.Name = 'Guru'
GROUP BY UserId
),
nice_answer AS 
(
SELECT UserId, count(*) AS badges
FROM Badges
WHERE Badges.Name = 'Nice Answer'
GROUP BY UserId
),
good_answer AS 
(
SELECT UserId, count(UserId) AS badges
FROM Badges
WHERE Badges.Name = 'Good Answer'
GROUP BY UserId
),
great_answer AS 
(
SELECT UserId, count(UserId) AS badges
FROM Badges
WHERE Badges.Name = 'Great Answer'
GROUP BY UserId
),
populist AS 
(
SELECT UserId, count(*) AS badges
FROM Badges
WHERE Badges.Name = 'Populist'
GROUP BY UserId
),
reversal AS 
(
SELECT UserId, count(*) AS badges
FROM Badges
WHERE Badges.Name = 'Reversal'
GROUP BY UserId
),
revival AS 
(
SELECT UserId, count(*) AS badges
FROM Badges
WHERE Badges.Name = 'Revival'
GROUP BY UserId
),
necromancer AS
(
SELECT UserId, count(*) AS badges
FROM Badges
WHERE Badges.Name = 'Necromancer'
GROUP BY UserId
),
self_learner AS
(
SELECT UserId, count(*) AS badges
FROM Badges
WHERE Badges.Name = 'Self-Learner'
GROUP BY UserId
),
tenacious AS
(
SELECT UserId, count(*) AS badges
FROM Badges
WHERE Badges.Name = 'Tenacious'
GROUP BY UserId
),
unsung_hero AS
(
SELECT UserId, count(*) AS badges
FROM Badges
WHERE Badges.Name = 'Unsung Hero'
GROUP BY UserId
),
bronze_tag AS
(
SELECT UserId, count(*) AS badges
FROM Badges
WHERE Badges.TagBased = 'true'
      AND Badges.Class = 3
GROUP BY UserId
),
silver_tag AS
(
SELECT UserId, count(*) AS badges
FROM Badges
WHERE Badges.TagBased = 'true'
      AND Badges.Class = 2
GROUP BY UserId
),
gold_tag AS
(
SELECT UserId, count(*) AS badges
FROM Badges
WHERE Badges.TagBased = 'true'
      AND Badges.Class = 1
GROUP BY UserId
)
SELECT u.Id AS [User Link],
       ROW_NUMBER() OVER (ORDER BY ROUND(
                                   (SELECT AVG(w) 
                                    FROM 
                                    (VALUES
                                     (CAST(ISNULL(e.badges,0) AS FLOAT)/CAST(@total_enlightened AS FLOAT)),
                                     (CAST(ISNULL(ex.badges,0) AS FLOAT)/CAST(@total_explainer AS FLOAT)),
                                     (CAST(ISNULL(rf.badges,0) AS FLOAT)/CAST(@total_refiner AS FLOAT)),
                                     (CAST(ISNULL(ill.badges,0) AS FLOAT)/CAST(@total_illuminator AS FLOAT)),
                                     (CAST(ISNULL(gen.badges,0) AS FLOAT)/CAST(@total_generalist AS FLOAT)),
                                     (CAST(ISNULL(g.badges,0) AS FLOAT)/CAST(@total_guru AS FLOAT)),
                                     (CAST(ISNULL(n.badges,0) AS FLOAT)/CAST(@total_nice_answer AS FLOAT)),
                                     (CAST(ISNULL(gda.badges,0) AS FLOAT)/CAST(@total_good_answer AS FLOAT)),
                                     (CAST(ISNULL(gra.badges,0) AS FLOAT)/CAST(@total_great_answer AS FLOAT)),
                                     (CAST(ISNULL(p.badges,0) AS FLOAT)/CAST(@total_populist AS FLOAT)),
                                     (CAST(ISNULL(rsal.badges,0) AS FLOAT)/CAST(@total_reversal AS FLOAT)),
                                     (CAST(ISNULL(rval.badges,0) AS FLOAT)/CAST(@total_revival AS FLOAT)),
                                     (CAST(ISNULL(ncer.badges,0) AS FLOAT)/CAST(@total_necromancer AS FLOAT)),
                                     (CAST(ISNULL(sf.badges,0) AS FLOAT)/CAST(@total_self_learner AS FLOAT)),
                                     (CAST(ISNULL(t.badges,0) AS FLOAT)/CAST(@total_tenacious AS FLOAT)),
                                     (CAST(ISNULL(uh.badges,0) AS FLOAT)/CAST(@total_unsung_hero AS FLOAT)),
                                     (CAST(ISNULL(btag.badges,0) AS FLOAT)/CAST(@total_bronze_tag AS FLOAT)),
                                     (CAST(ISNULL(stag.badges,0) AS FLOAT)/CAST(@total_silver_tag AS FLOAT)),
                                     (CAST(ISNULL(gtag.badges,0) AS FLOAT)/CAST(@total_gold_tag AS FLOAT))
                                    ) T (w)
                                   ) * 100 , 2) DESC,
                                   (ISNULL(e.badges,0) +
                                    ISNULL(ex.badges,0) +
                                    ISNULL(rf.badges,0) +
                                    ISNULL(ill.badges,0) +
                                    ISNULL(gen.badges,0) +
                                    ISNULL(g.badges,0) +
                                    ISNULL(n.badges,0) +
                                    ISNULL(gda.badges,0) +
                                    ISNULL(gra.badges,0) +
                                    ISNULL(p.badges,0) +
                                    ISNULL(rsal.badges,0) +
                                    ISNULL(rval.badges,0) +
                                    ISNULL(ncer.badges,0) +
                                    ISNULL(sf.badges,0) +
                                    ISNULL(t.badges,0) +
                                    ISNULL(uh.badges,0) +
                                    ISNULL(btag.badges,0) +
                                    ISNULL(stag.badges,0) +
                                    ISNULL(gtag.badges,0)) DESC,
                                   n.badges DESC
                         ) AS [Rank#],
       ROUND(
          (SELECT AVG(w) 
           FROM (VALUES
            (CAST(ISNULL(e.badges,0) AS FLOAT)/CAST(@total_enlightened AS FLOAT)),
            (CAST(ISNULL(ex.badges,0) AS FLOAT)/CAST(@total_explainer AS FLOAT)),
            (CAST(ISNULL(rf.badges,0) AS FLOAT)/CAST(@total_refiner AS FLOAT)),
            (CAST(ISNULL(ill.badges,0) AS FLOAT)/CAST(@total_illuminator AS FLOAT)),
            (CAST(ISNULL(gen.badges,0) AS FLOAT)/CAST(@total_generalist AS FLOAT)),
            (CAST(ISNULL(g.badges,0) AS FLOAT)/CAST(@total_guru AS FLOAT)),
            (CAST(ISNULL(n.badges,0) AS FLOAT)/CAST(@total_nice_answer AS FLOAT)),
            (CAST(ISNULL(gda.badges,0) AS FLOAT)/CAST(@total_good_answer AS FLOAT)),
            (CAST(ISNULL(gra.badges,0) AS FLOAT)/CAST(@total_great_answer AS FLOAT)),
            (CAST(ISNULL(p.badges,0) AS FLOAT)/CAST(@total_populist AS FLOAT)),
            (CAST(ISNULL(rsal.badges,0) AS FLOAT)/CAST(@total_reversal AS FLOAT)),
            (CAST(ISNULL(rval.badges,0) AS FLOAT)/CAST(@total_revival AS FLOAT)),
            (CAST(ISNULL(ncer.badges,0) AS FLOAT)/CAST(@total_necromancer AS FLOAT)),
            (CAST(ISNULL(sf.badges,0) AS FLOAT)/CAST(@total_self_learner AS FLOAT)),
            (CAST(ISNULL(t.badges,0) AS FLOAT)/CAST(@total_tenacious AS FLOAT)),
            (CAST(ISNULL(uh.badges,0) AS FLOAT)/CAST(@total_unsung_hero AS FLOAT)),
            (CAST(ISNULL(btag.badges,0) AS FLOAT)/CAST(@total_bronze_tag AS FLOAT)),
            (CAST(ISNULL(stag.badges,0) AS FLOAT)/CAST(@total_silver_tag AS FLOAT)),
            (CAST(ISNULL(gtag.badges,0) AS FLOAT)/CAST(@total_gold_tag AS FLOAT))
          ) T (w)) * 100 , 2) AS [Weight %], -- Weight in % (sum of weights from all users equals 100%)
       (ISNULL(e.badges,0) +
        ISNULL(ex.badges,0) +
        ISNULL(rf.badges,0) +
        ISNULL(ill.badges,0) +
        ISNULL(gen.badges,0) +
        ISNULL(g.badges,0) +
        ISNULL(n.badges,0) +
        ISNULL(gda.badges,0) +
        ISNULL(gra.badges,0) +
        ISNULL(p.badges,0) +
        ISNULL(rsal.badges,0) +
        ISNULL(rval.badges,0) +
        ISNULL(ncer.badges,0) +
        ISNULL(sf.badges,0) +
        ISNULL(t.badges,0) +
        ISNULL(uh.badges,0) +
        ISNULL(btag.badges,0) +
        ISNULL(stag.badges,0) +
        ISNULL(gtag.badges,0)
        ) AS [Total], -- Total number of answer badges
       e.badges AS [Enlightened],
       ex.badges AS [Explainer],
       rf.badges AS [Refiner],
       ill.badges AS [Illuminator],
       gen.badges AS [Generalist],
       g.badges AS [Guru],
       n.badges AS [Nice Answer],
       gda.badges AS [Good Answer],
       gra.badges AS [Great Answer],
       p.badges AS [Populist],
       rsal.badges AS [Reversal],
       rval.badges AS [Revival],
       ncer.badges AS [Necromancer],
       sf.badges AS [Self-Learner],
       t.badges AS [Tenacious],
       uh.badges AS [Unsung Hero],
       btag.badges AS [Bronze Tag],
       stag.badges AS [Silver Tag],
       gtag.badges AS [Gold Tag],
       u.Reputation
FROM Users u
FULL OUTER JOIN enlightened e ON e.UserId = u.Id
FULL OUTER JOIN explainer ex ON ex.UserId = u.Id
FULL OUTER JOIN refiner rf ON rf.UserId = u.Id
FULL OUTER JOIN illuminator ill ON ill.UserId = u.Id
FULL OUTER JOIN generalist gen ON gen.UserId = u.Id
FULL OUTER JOIN guru g ON g.UserId = u.Id
FULL OUTER JOIN nice_answer n ON n.UserID = u.Id
FULL OUTER JOIN good_answer gda ON gda.UserID = u.Id
FULL OUTER JOIN great_answer gra ON gra.UserID = u.Id
FULL OUTER JOIN populist p ON p.UserID = u.Id
FULL OUTER JOIN reversal rsal ON rsal.UserID = u.Id
FULL OUTER JOIN revival rval ON rval.UserID = u.Id
FULL OUTER JOIN necromancer ncer ON ncer.UserID = u.Id
FULL OUTER JOIN self_learner sf ON sf.UserID = u.Id
FULL OUTER JOIN tenacious t ON t.UserID = u.Id
FULL OUTER JOIN unsung_hero uh ON uh.UserID = u.Id
FULL OUTER JOIN bronze_tag btag ON btag.UserID = u.Id
FULL OUTER JOIN silver_tag stag ON stag.UserID = u.Id
FULL OUTER JOIN gold_tag gtag ON gtag.UserID = u.Id
GROUP BY u.Id,  
         e.badges,
         ex.badges,
         rf.badges,
         ill.badges,
         gen.badges,
         g.badges,
         n.badges,
         gda.badges,
         gra.badges,
         p.badges,
         rsal.badges,
         rval.badges,
         ncer.badges,
         sf.badges,
         t.badges,
         uh.badges,
         btag.badges,
         stag.badges,
         gtag.badges,
         u.reputation
HAVING ISNULL(e.badges,0) +
       ISNULL(ex.badges,0) +
       ISNULL(rf.badges,0) +
       ISNULL(ill.badges,0) +
       ISNULL(gen.badges,0) +
       ISNULL(g.badges,0) +
       ISNULL(n.badges,0) +
       ISNULL(gda.badges,0) +
       ISNULL(gra.badges,0) +
       ISNULL(p.badges,0) +
       ISNULL(rsal.badges,0) +
       ISNULL(rval.badges,0) +
       ISNULL(ncer.badges,0) +
       ISNULL(sf.badges,0) +
       ISNULL(t.badges,0) +
       ISNULL(uh.badges,0) +
       ISNULL(btag.badges,0) +
       ISNULL(stag.badges,0) +
       ISNULL(gtag.badges,0) >= ##total:int?50## --at least 'n' answer badges
       -- total: Minimum amount of answer badges (per user):
ORDER BY [Weight %] DESC,
         [Total] DESC,
         n.badges DESC;

Enter Parameters

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