5

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.

3

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. – Monica Cellio 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. – Monica Cellio Oct 27 '14 at 1:08
1

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:

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.