I posted questions. Some got closed. I'd like to see which users close voted them most often.

E.g., display: user X close voted to close 23 of my questions, user Y close voted to close 17 of my questions, etc.

I believe we can only see users who submitted close votes for closed questions, so I'm ok to sort users by how many closed questions I posted they close voted.

FYI: What's the goal of listing the users that voted to close a question?


I'll leave the ethical discussion about how this analysis can be (ab)used to others and focus just on how you can obtain this data.

SEDE has a table PostHistory that stores in a row with PostHistoryTypeId = 10 the successful closure of a Post. (Close votes or flags that didn't lead to an actual closure are not available in SEDE). In the Text column a JSON is stored that has a Voters property with an array of objects where for each voter Id, DisplayName and whether they used their binding gold badge hammer.

T-SQL has the OPENJSON function that can be used to transform JSON arrays into proper tables so they can be queried and/or projected as for any other column. Here I use a CROSS join after your questions are selected.

Combining all this leads to this SEDE query:

select v.Id [User Link]
     , count(*) [# Closed]
from posthistory ph
inner join posts p on p.id = ph.postid
cross apply openjson(text, '$.Voters') with (Id integer  '$.Id') v
where ph.posthistorytypeid = 10 -- closed
and p.owneruserid = ##userid:int?395857##
group by v.id
order by count(*) desc

When run today on your Stack Overflow account, no users have been involved in close voting a question of yours more then once

Result of the query as run on 2020-07-24, showing links to users and their count, all counts are 1

It is rather uninteresting.

As this information should remain private according to some users let's also have a look at the Network Wide Close voters of Franck.

Who could have guessed, he is close vote stalking himself, further some moderators and a gold badge holder on MSE.

shows Frank as top result, second is David Postil, third is Rob

That is more interesting.

Keep in mind SEDE is updated once a week on Sunday.
Use the awesome SEDE Tutorial written by the unforgettable Monica Cellio.
Say "Hi" in SEDE chat.


You must log in to answer this question.

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