Which answers have earned the most badges?

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Which answers have earned the most badges?

SELECT TOP 100 answerid, questionid, answeruserid, questionuserid,
       NiceAnswer + GoodAnswer + GreatAnswer + Guru + Enlightened + Reversal + Populist + Necromancer + Editor + Cleanup as TotalBadges,
       NiceAnswer, GoodAnswer, GreatAnswer, Guru, Enlightened,
       Reversal, Populist, Necromancer, Editor, Cleanup
FROM 
(
select a.id as answerid,
       q.id as questionid,
       a.owneruserid as answeruserid,
       q.owneruserid as questionuserid,
       -- Nice Answer: score at least 10
       CASE WHEN a.score >= 10  THEN 1 ELSE 0 END AS NiceAnswer,
       -- Good Answer: score at least 25
       CASE WHEN a.score >= 25  THEN 1 ELSE 0 END AS GoodAnswer,
       -- Great Answer: score at least 100
       CASE WHEN a.score >= 100 THEN 1 ELSE 0 END AS GreatAnswer,
       -- Guru: accepted and score at least 40
       CASE WHEN a.score >= 40  
             AND a.id = q.acceptedanswerid 
             AND a.owneruserid != q.owneruserid 
            THEN 1 ELSE 0 END AS Guru,
       -- Enlightened: accepted, score at least 10, and first answer 
       CASE WHEN a.score >= 10
             AND a.id = q.acceptedanswerid
             AND a.owneruserid != q.owneruserid
             AND a.id = q.firstanswerid
            THEN 1 ELSE 0 END AS Enlightened,
       -- Reversal: score at least 20, answer score at most -5
       CASE WHEN a.score >= 20 AND q.score <= -5 
            THEN 1 ELSE 0 END AS Reversal,
       -- Populist: highest scoring answer doubling accepted score of > 10
       CASE WHEN q.score > 10
             AND q.highestscoringanswerscore = a.score
             AND a.id != q.acceptedanswerid
             AND a.owneruserid != q.owneruserid
             AND a.score - q.acceptedanswerscore > q.acceptedanswerscore
            THEN 1 ELSE 0 END AS Populist,
       -- Necromancer: answer score >= 5, posted >= 60 days after question
       CASE WHEN a.score >= 5 
             AND datediff(day, q.creationdate, a.creationdate) >= 60
            THEN 1 ELSE 0 END AS Necromancer,
       -- Editor: edit first post
       CASE WHEN u.firsteditedpostid = a.id THEN 1 ELSE 0 END AS Editor,
       -- Cleanup: rollback first post
       CASE WHEN u.firstrollbackpostid = a.id THEN 1 ELSE 0 END AS Cleanup
        
       
  from posts a
  join (select q.id, q.acceptedanswerid, q.owneruserid, q.score,
               q.creationdate,
               aa.score AS acceptedanswerscore,
               min(a.id) AS firstanswerid,
               max(a.score) AS highestscoringanswerscore
          from posts q
          join posts a on q.id = a.parentid
          join posts aa on q.acceptedanswerid = aa.id
         where q.posttypeid = 1
         group by q.id, q.acceptedanswerid, q.owneruserid, q.score,
                  q.creationdate, aa.score
       ) q
    on a.parentid = q.id
  join (select fe.userid, fe.firsteditedpostid, fr.firstrollbackpostid
          from (select firstedit.userid, 
                       posthistory.postid as firsteditedpostid
                  from posthistory
                  join (select userid, 
                               min(id) as firsteditid from posthistory
                         where posthistorytypeid in (4,5)
                         group by userid
                       ) firstedit
                     on posthistory.id = firstedit.firsteditid
               ) fe
          join (select firstrollback.userid, 
                       posthistory.postid as firstrollbackpostid
                  from posthistory
                  join (select userid, 
                               min(id) as firstrollbackid from posthistory
                         where posthistorytypeid in (7,8,9)
                         group by userid
                       ) firstrollback
                     on posthistory.id = firstrollback.firstrollbackid
               ) fr
            on fe.userid = fr.userid
       ) u
    on a.owneruserid = u.userid
 where a.posttypeid = 2
) thequery
ORDER BY totalbadges DESC

Enter Parameters

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