3

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.

| |
  • So there is a Votes table that contains close/reopen/delete vote records. However, the UserId's are omitted from these records (there's like some MSE posts on this). The only votes that UserId is recorded for in SEDE are Favorite and Bounty Start. As such, I'm not sure if there's an easy way to get the data you're looking for. – scohe001 Feb 17 at 15:23
  • @scohe001 Arf :/ So, I guess the solution is "do it by hand or ask a SE employe" then – Ael Feb 17 at 16:55
  • 1
    I have a hacky solution, but it'll only catch cases where the question was actually closed or reopened (which kind of makes sense since all other votes will be aged away and deleted from the db eventually anyways I guess?). I'll post the query in a minute here. – scohe001 Feb 17 at 17:05
4

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).

| |
  • That's awesome! Thank scohe! – Ael Feb 17 at 17:33
  • Happy to help! :) – scohe001 Feb 17 at 17:34
  • I might be wrong, but I think that the PostHistory table contain data only about posts which are not deleted. (I believe the only data about deleted posts are the ones in the PostsWithDeleted table.) So this means that if we check deleted votes, we only see questions which were later undeleted. Is that correct or did I miss something? – Martin Sleziak Feb 19 at 7:48
  • Huh, you know that actually sounds right @Martin. I was wondering why those numbers looked so small. I'll add a note to the post. Thanks for pointing that out!! – scohe001 Feb 19 at 13:54

You must log in to answer this question.

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