1

Please login or register to vote for this query.

(click on this box to dismiss)

Lists all users with the necessary reputation and badges. See: http://stackoverflow.com/election/2 Ordered by badge count, as this may be better guide than raw rep. NOTE: requirements changing rapidly! See http://meta.stackoverflow.com/questions/111302#111307

Q&A for anime and manga fans

-- Possible candidates for the Nov, 2011 moderator election.
-- Lists all users with the necessary reputation and badges.
-- See: http://stackoverflow.com/election/2
-- Ordered by badge count, as this may be better guide than raw rep.
-- NOTE: requirements changing rapidly!  See http://meta.stackoverflow.com/questions/111302#111307

WITH hasReqBadges AS (
    SELECT
                b.UserId,
                COUNT (DISTINCT b.Name) AS [badgeCnt]
    FROM        Badges b
    WHERE       b.Name IN ( 'Civic Duty'
                            , 'Strunk & White'
                            , 'Deputy'
                            , 'Convention'
                            --, 'Yearling'
                          )
    GROUP BY    b.UserId
)
SELECT
            hrb.UserId AS [User Link],
            u.Reputation,
            (   SELECT  COUNT (b.Id)
                FROM    Badges b
                WHERE   hrb.UserId = b.UserId
            ) AS [Badge cnt]

FROM        hasReqBadges hrb
INNER JOIN  Users  u    ON hrb.UserId = u.Id
WHERE       u.Reputation >= 3000
AND         hrb.badgeCnt = 4    -- Coordinate with in() list, above.
ORDER BY    [Badge cnt] desc,
            u.Reputation desc

fork query

created nov 7 11 Awesome Poodles