Goal: Make the list of Top 50 users who closed most questions on a site.

This means the questions that actually got closed, not pending close votes/flags. Deleted questions are excluded from consideration, since they are not available in Data Explorer.

Using the PostHistory table, it is easy to find how many questions were closed by a particular user: example. But I don't see a way to produce the list of Top N closers.

The relevant column of PostHistory table is Text, which contains the list of voters in object notation:

{"Voters":[{"Id":11176,"DisplayName":"TMM"},{"Id":9849,"DisplayName":"Davide Giraudo"},{"Id":125084,"DisplayName":"symmetricuser"},{"Id":36150,"DisplayName":"saz"},{"Id":147263,"DisplayName":"Weapon of Choice"}]}

So, if I download the rows with PostHistoryTypeId=10 and run some script on the data, I can make a Top 50 list this way. But this is a really awkward way: it would be much better to have a SEDE query returning such a list. Is this even possible? My knowledge of SQL is not enough for this.


This query returns the top closers, counting only questions that were ultimately closed. It doesn't reflect questions that were later deleted (SEDE doesn't know about deleted posts), and I don't know what it does with questions that were later reopened.

This query will time out for large sites (don't try it on the trilogy). If I were better at SQL I might be able to figure out how to constrain it to only look at users who've cast a (specified) minimum number of close votes.

h/t to Jim G on Workplace for making me aware of this query, which I've edited slightly. You'll find links to some other site-analytics queries there.

  • Thanks! Yes, the part "Add Mark Trapp" is funny; I wonder what's the history behind that. The query looks at every user on the site with reputation >=500, looking up PostHistory individually for each of them. I could make that >=3000 for graduated site, but even then there are way too many users on Math for the query to finish: it times out. I'll look into a mixed SEDE/script solution, then.
    – user259867
    Oct 27 '14 at 0:50
  • @WeaponofChoice yeah, you might want to go ahead and parameterize the rep threshold, but it's still going to be an expensive query. You could maybe refine it by setting a lower threshold on the number of closes (don't even bother to look at users who haven't cast at least N close votes). I'd parameterize that too. Oct 27 '14 at 0:54
  • @WeaponofChoice I made some edits to the query to de-Mark it and let you specify the rep threshold. I can't figure out how to constrain for the number of votes in a way that's any more efficient than the current code. I updated the link in my answer. Oct 27 '14 at 1:08

With the newer version of SQL Server the function JSON_VALUE got added. That makes the parsing of that JSON payload way easier.

Lazy as we are we string_split 0,1,3,4,5,6 on the comma to act as a set for our index of the voter which we cross join with the posthistory table. The JSON_VALUE accepts a json path expression where $ indicates the top node and from there we can reach the attributes within thwe Voters JSON object by building a query string with our index value, like so $.Voters[0].Id. Once done all we need is to project the results.

Here is that query.

;with closevoters as
select convert(int,json_value( 
        text, concat(
          )) as voterid
from posthistory ph
cross join string_split('0,1,2,3,4,5,6', ',') ind
where posthistorytypeid = 10 -- close event

select voterid as [User Link]
     , count(*) [# of posts closed]
from closevoters
where voterid is not null
group by voterid
order by count(*) desc

When run today on the Workplace your result will be:

top close voters

Keep in mind SEDE only holds this kind of data for non-deleted posts. If you're interested in including the deleted posts you have to ask a CM.

Oh, and this query does run on Stack Overflow, despite the huge number of rows it needs to process. SQL Server does a pretty awesome job in that respect to parse that JSON.

SEDE is updated once a week on Sunday morning. Do give the tutorial a try and say Hi in chat

You must log in to answer this question.