Which questions 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 questions have earned the most badges?

SELECT TOP 200 questionid, title, questionuserid,
       NiceQuestion + GoodQuestion + GreatQuestion + PopularQuestion + NotableQuestion + FamousQuestion + FavoriteQuestion + StellarQuestion + Editor + Cleanup + Promoter as TotalBadges,
       NiceQuestion, GoodQuestion, GreatQuestion, 
       PopularQuestion, NotableQuestion, FamousQuestion,
       FavoriteQuestion, StellarQuestion,
       Editor, Cleanup,
       Promoter
FROM 
(
select q.id as questionid,
       q.title as title,
       q.owneruserid as questionuserid,
       -- Nice Question: score at least 10
       CASE WHEN q.score >= 10  THEN 1 ELSE 0 END AS NiceQuestion,
       -- Good Question: score at least 25
       CASE WHEN q.score >= 25  THEN 1 ELSE 0 END AS GoodQuestion,
       -- Great Question: score at least 100
       CASE WHEN q.score >= 100 THEN 1 ELSE 0 END AS GreatQuestion,
       -- Popular Question: at least 1000 views
       CASE WHEN q.viewcount >= 1000 THEN 1 ELSE 0 END AS PopularQuestion,
       -- Notable Question: at least 2500 views
       CASE WHEN q.viewcount >= 2500 THEN 1 ELSE 0 END AS NotableQuestion,
       -- Famous Question: at least 10000 views
       CASE WHEN q.viewcount >= 10000 THEN 1 ELSE 0 END AS FamousQuestion,
       -- Favorite Question: at least 25 favorites
       CASE WHEN q.favoritecount >= 25  THEN 1 ELSE 0 END AS FavoriteQuestion,
       -- Stellar Question: at least 100 favorites
       CASE WHEN q.favoritecount >= 100 THEN 1 ELSE 0 END AS StellarQuestion,
       -- Editor: edit first post
       CASE WHEN u.firsteditedpostid = q.id THEN 1 ELSE 0 END AS Editor,
       -- Cleanup: rollback first post
       CASE WHEN u.firstrollbackpostid = q.id THEN 1 ELSE 0 END AS Cleanup,
       -- Promoter: first bounty offered on own question
       CASE WHEN u.firstbountyofferedid = q.id THEN 1 ELSE 0 END AS Promoter
  from posts q
  left join 
       (select fe.userid,
               fe.firsteditedpostid,
               fr.firstrollbackpostid,
               fbo.firstbountyofferedid
          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
          left 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
          left join
               (select firstbountyoffered.userid,
                       votes.postid as firstbountyofferedid
                  from votes
                  join (select votes.userid, min(votes.id) as firstbountyofferedid
                          from votes
                          join posts
                            on votes.postid = posts.id
                         where votetypeid = 8
                           and votes.userid = posts.owneruserid
                         group by userid
                       ) firstbountyoffered
                    on votes.id = firstbountyofferedid
               ) fbo
            on fe.userid = fbo.userid
                       
       ) u
    on q.owneruserid = u.userid
 where q.posttypeid = 1
) thequery
ORDER BY totalbadges DESC

Enter Parameters

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