How to use Data Explorer to find the most prolific closers on a site?

Question

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.

Answer 1

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.

Answer 2

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(
          '$.Voters[', 
          ind.value, 
          '].Id')
          )) 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:

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