How many people are helping to moderate this site?

Question

For a SE site to be successful, we need people to help moderate it. The more help, the better.

So, I'm wondering, could we have some information about how many different people help moderate this site?

Information like:

• How many different people voted to close a question in the last 1 months, 3 months and 6 months?

• How many different people voted to reopen a question in the last 1 months, 3 months and 6 months?

• How many different people voted to delete an answer in the last 1 months, 3 months and 6 months?

I would also be interested in knowing how many different people raised a (valid) flag in the last 1 months, 3 months and 6 months, but those are not public information, so it might not be possible to know that.

Answer 1

Click here to see the query I wrote to fetch those numbers (Action type must be one of {10 - Close, 11 - Reopen, 12 - Delete, 13 - Undelete}).

Like I mentioned in a comment, we have a Votes table that has all of the close/reopen/delete votes (in addition to the rest). However, UserId's aren't recorded for those in an attempt to keep some privacy.

So to find this data, we need to be a little...less conventional. There's a PostHistory table that contains actions taken on a post (delete/undelete/close/reopen), and in that, there's a JSON column that contains a list of users who took action on the post (if applicable).

All this to say, pulling the data from PostHistory is possible, but this will only give us users who contributed a vote to a question that was eventually closed/reopened/deleted/undeleted, not any that made votes that then aged away. In addition, only data for non-deleted questions is available here, so you'll only be seeing delete/undelete votes for questions that were eventually undeleted.

Let me know if you have any questions about it or if you need more data!

-- at: Action Type "10 - Close, 11 - Reopen, 12 - Delete, 13 - Undelete"
-- sd: Start date "yyyy-mm-dd format"
-- ed: End date "yyyy-mm-dd format"

DECLARE @atype INT = ##at:int##
DECLARE @sdate NVARCHAR(10) = ##sd:string##
DECLARE @edate NVARCHAR(10) = ##ed:string##
DECLARE @startdate DATE = CAST(@sdate as date)
DECLARE @enddate DATE = CAST(@edate as date)

SELECT
  ph.PostId, ph.CreationDate, voter_id INTO #voter_ids
FROM PostHistory ph
CROSS APPLY OPENJSON (ph.Text)
  WITH (
    voters NVARCHAR(MAX) '$.Voters' AS JSON
  )
  OUTER APPLY OPENJSON(voters)
  WITH (voter_id NVARCHAR(20) '$.Id')
WHERE ph.PostHistoryTypeId = @atype -- Close 
  AND ph.CreationDate >= @startdate AND ph.CreationDate < @enddate

SELECT 
  COUNT(DISTINCT voter_id) [Num Voters] 
FROM
  #voter_ids
WHERE
  voter_id != -1 -- Don't count the deleted user

Just for completeness sake, here's a modified version of the query that lists who voted and how many times they voted (ordered by most votes).