How can I sort users by how many of my questions they close voted?

Question

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?

Answer 1

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

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.

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.