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:
- Click Run Code Snippet below the code.
- 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".
- Press the Generate Data button.
- 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.
