27

Moderators are special, privileged users who should serve as an example to the rest of us. Therefore, it is natural to want to query behaviors and statistics about these model users, Like I did, for example, when researching this question.

Unfortunately, there is no way to determine moderators in the SEDE data set! We are left with hand-coding the current mods like in this query. This is hardly a good solution and does not port if we want to quickly switch the query from, say, SO to Super User.

Please provide a Moderators table, listing the ID's of that site's moderators. (Or, alternatively, add a flag-column to the Users table, but this would be less efficient.)

As nhinkle notes, this information is already in the API. Please add it to SEDE, as well.



For reference, the identities of the mods is by no means secret -- being posted on each site's About page.

It's just very clunky to get this list inside a query. Currently, you can kludge a list of moderators by navigating to an About page and executing this javascript:

$("#mainbar-full div.content-page div a[href*='/users/']").map ( function () {
    return parseInt (this.href.replace (/.+\/users\/(\d+)\/.+$/, "$1"), 10);
} ).get ();

Alternatively, parse the return from the API.

  • 1
    Go to the users page > Get their ID number? – Simon Sheehan Dec 10 '11 at 23:18
  • 8
    @SimonSheehan, that does no good inside a query. Getting a list of mods is not difficult for the public. Getting a list of mods in a query, is another matter. – Awesome Poodles Dec 10 '11 at 23:23
  • @BrockAdams FYI there is an easier way to automate it than by using that jquery call... a list of mods is provided by the API. That being said, providing it in SEDE would make things easier. – nhinkle Dec 11 '11 at 0:07
  • 1
    Thanks, @nhinkle! Now if we can get that information into the Data Explorer, it would be great. – Awesome Poodles Dec 11 '11 at 0:11
  • They probably doesn't have a moderator timeline table, or at most in some not very well-indexed logs. I think this on the reason that system functions (for example, question undelete-ability) depends on the current moderator list and not on the history. For example, if a mod is demodded, the questions deleted by him become undeletable. – peterh Mar 31 '17 at 21:35
  • It's weird, I found this old migration from 2011 titled "Drop meaningless IsModerator from Users.sql". – Jason C Apr 10 '17 at 2:27
7

First of all, yes please. I'd love to see this old request revisited. If not a dedicated table, then a field in Users that indicates mod/CM status.


While not a completely SEDE-only automated way of doing this, I wrote a script to generate a query for you. To use the snippet below:

  1. Click Run Code Snippet below the code.
  2. Enter the API site name (see per-site methods section) (e.g. "meta" gets MSE, "stackoverflow" gets SO). Note: The API accepts the site domain name as well, like "softwareengineering.stackexchange.com".
  3. Press the Generate Data button.
  4. This will generate some SQL that creates a temporary table named #Diamonds and populates it, which you can paste at the start of your own query, then you can use that table in joins or whatever.

Here is an example for SO as of the time of this writing. I included fields that I thought would be convenient, additional data can be obtained by joining with Users. The AccountId column is the network ID so if all you want is e.g. a list of employees you could, for example, generate data for MSE then just use AccountId as necessary instead of UserId, which works no matter what site you run the query against.

Here is a functional example which shows the most recent 50 comments by non-employee moderators on SO.

$('#update').click(function() {
  updateEmployees($('#site').val());
});

function updateEmployees(site) {
  $.getJSON(`https://api.stackexchange.com/2.2/users/moderators?pagesize=100&order=desc&sort=name&site=${site}&filter=0cXJuGggI2Kabw5kJ`).done(function(data) {
    var q = `-- Diamond data from ${site} on ${new Date().toISOString()} (${data.items.length} rows).\n`;
    q += '-- More info: https://meta.stackexchange.com/a/291587\n';
    q += 'CREATE TABLE #Diamonds (AccountId int, UserId int, IsEmployee bit, CreationDate datetime, DisplayName nvarchar(max));\n'
    var count = 0;
    $.each(data.items, function(_, u) {
      var name = u.display_name.replace(/'/g, "''");
      var date = new Date(u.creation_date * 1000).toISOString().replace(/T/, ' ').replace(/\.[0-9]{3}Z/, '');
      q += `INSERT INTO #Diamonds VALUES (${u.account_id}, ${u.user_id}, ${u.is_employee ? 1 : 0}, '${date}', '${name}')\n`
    });
    $('#query').text(q);
    $('#status').text(`Success! Quota remaining: ${data.quota_remaining}`);
    if (data.has_more)
      alert('Data is not complete, has_more not supported by this script.');
  }).fail(function(e) {
    $('#query').text('');
    $('#status').text(`Failed: ${e.responseText ? e.responseText : e.statusText}`);
  });
}
<html>

<head>
  <script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.js"></script>
</head>

<body>
  <div>
    <textarea id="query" style="width:100%" rows="5"></textarea>
  </div>
  <div>
    <input id="site" value="meta">
    <button id="update">Generate Data</button>
  </div>
  <div id="status"></div>
</body>


Also, you can get a partial list by taking advantage of the fact that only mods can issue non-bounty post notices (correct me if I'm wrong here) with a query like this:

SELECT
  u.Id AS [User Link],
  *
FROM 
  (
    SELECT
      DISTINCT n.OwnerUserId
    FROM
      PostNotices n
      INNER JOIN PostNoticeTypes t ON n.PostNoticeTypeId = t.Id
    WHERE
      t.ClassId IN (1, 4) -- http://meta.stackexchange.com/a/2678/230261
  ) m
  INNER JOIN Users u ON u.Id = m.OwnerUserId

This list isn't 100%, unfortunately, but it was the closest I was able to get. Notes:

  • It makes no distinction between site mods and CMs.
  • It may include users who used to be mods/CMs but are no longer.
  • It omits mods/CMs who have never issued a notice.

If anybody can think of something better along these lines, please share, I'd really like to be able to do this.

You can sort of supplement this list on some sites by also examining PostHistory to look for users who have made edits that are neither suggested edits nor self-edits and who also don't have enough rep to make edits. That sometimes snags a couple extra but probably not worth the returns for the effort. I'll leave the query as an exercise to the reader (also it's not worth including) but it can be based off the InstantEditors part of the query here.

  • 1
    I was looking to find something in the PostHistory table (specifically close reasons from the Text field), but I couldn't find anything related to moderators. – Patrick Hofman Feb 27 '17 at 9:55
  • @PatrickHofman I added a script to generate a table from the API, if you care. – Jason C May 1 '17 at 18:13
  • 1
    -1 for me having to guess the site_api_parameter .. :( – rene May 1 '17 at 19:18
  • @jason I do. Thanks for pinging me even so late. – Patrick Hofman May 1 '17 at 20:13
5

Yes, please. This ought to be a status column on the User, like it apparently once was. A status column could possibly include other interesting status values, too, should SE choose to expose them. For example, while community managers and some other employees have diamonds, the system knows that they're a different kind of diamond than moderators -- so that could be exposed. "Who are the moderators" and "who are the users with moderator powers" are not the same question.

2

If you ever need a network-wide list of moderators in a SEDE query, e.g. to answer Are there any questions closed by 5 moderators?, you can use the following snippet on the global moderator list:

var sql = "CREATE TABLE #mods (site nvarchar(50), userid int, username nvarchar(50));";
sql += "INSERT INTO #mods VALUES ";
$("a.mods-highlightable[href*='/users/']").each(function(index) {
    var href = this.getAttribute("href");
    var regex = /^https:\/\/([\w.]*)\/users\/(\d+)\//g;
    var groups = regex.exec(href);
    sql += (index == 0 ? "" : ", ") + "('" + groups[1] + "', " + groups[2] + ", '"
        + this.innerText.replace(/'/g, "''") + "')";
});
sql += ";";
console.log(sql);

You'll have to combine this with a query to convert database names to site URLs, like this one. I'll try to keep these up-to-date in the future, but feel free to fork them when needed.

  • This is pretty clever! – rene Oct 2 '18 at 18:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .