7

Please login or register to vote for this query.

(click on this box to dismiss)

How many users have enough reputation for a given privilege? Data for ¿all? sites is included. It was taken from the API (https://api.stackexchange.com/docs/privileges). IMPORTANT: 1) The API data does not always agree with the FAQ... which does not always agree with the privilege pages! 2) There is currently no way for a SEDE query to know what site's data it is using. Hence, the PrivilegeRequirements are shown for all sites, whether they apply or not.

Q&A about the site for those committed to a vegan or vegetarian lifestyle and anyone interested in learning more

DECLARE @padStr NVARCHAR(64)    = NCHAR (8199) + NCHAR (8199) + NCHAR (8199) + NCHAR (8199)
                                + NCHAR (8199) + NCHAR (8199) + NCHAR (8199) + NCHAR (8199)
                                + NCHAR (8199) + NCHAR (8199) + NCHAR (8199) + NCHAR (8199)

DECLARE @PrivilegeRequirements TABLE (
    Privilege           VARCHAR(128),
    Site                VARCHAR(128),
    [Min Reputation]    INT
)

INSERT INTO
    @PrivilegeRequirements (Privilege, Site, [Min Reputation])
VALUES
    ('Access to moderator tools',               '*All*',             10000),
    ('Approve or reject tag wiki edits',        '*All*',              5000),
    ('Ask and answer questions',                '*All*',                 1),
    ('Cast close and reopen votes',             '*All*',              3000),
    ('Comment everywhere',                      'Main Meta',             1),
    ('Comment everywhere',                      'Most sites',           50),
    ('Comment everywhere',                      'Most sites, meta',     50),
    ('Comment everywhere',                      'Stack Overflow',       50),
    ('Comment everywhere',                      'Super User',            1),
    ('Comment everywhere',                      'Super User, meta',      1),
    ('Create chat rooms',                       '*All*',               100),
    ('Create community-wiki posts',             '*All*',                10),
    ('Create gallery chat rooms',               '*All*',              1000),
    ('Create new tags',                         'Main Meta',           500),
    ('Create new tags',                         'Most sites',          300),
    ('Create new tags',                         'Most sites, meta',    300),
    ('Create new tags',                         'Stack Overflow',     1500),
    ('Create new tags',                         'Super User',          500),
    ('Create new tags',                         'Super User, meta',    500),
    ('Edit community wiki questions',           '*All*',               100),
    ('Edit questions and answers',              '*All*',              2000),
    ('Established User',                        '*All*',              1000),
    ('Flag posts',                              '*All*',                15),
    ('Participate in per-site meta',            '*All*',                 5),
    ('Perform trusted functions on the site',   '*All*',             20000),
    ('Protect questions',                       '*All*',             15000),
    ('Reduced advertising',                     'Most sites',          200),
    ('Remove new user restrictions',            '*All*',                10),
    ('Retag questions',                         '*All*',               500),
    ('Set a bounty on a question',              '*All*',                75),
    ('Suggest and vote on tag synonyms',        '*All*',              2500),
    ('Talk in chat',                            '*All*',                20),
    ('View close votes',                        '*All*',               250),
    ('Vote down questions and answers',         '*All*',               125),
    ('Vote up questions and answers',           '*All*',                15),
    ('Total Users in the data dump for this site','==>',                -1)

SELECT
            pr.Privilege,
            pr.Site,
            pr.[Min Reputation],
            -- Make it look "purty"
            RIGHT (
                @padStr +
                REPLACE (
                    CONVERT (nvarchar(64), (CAST (COUNT (u.Id) AS MONEY) ), 1),
                    N'.00',
                    N''
                )
                , 13
            )
            AS [Number of Users]

FROM        @PrivilegeRequirements pr
LEFT JOIN   Users u
ON          u.Reputation >= pr.[Min Reputation]
GROUP BY
            pr.Privilege,
            pr.Site,
            pr.[Min Reputation]
ORDER BY
            pr.[Min Reputation],
            pr.Privilege,
            pr.Site

fork query

created dec 21 12 Awesome Poodles