5

I am trying to get the list of users that have the most number of close votes (tallying each type of close votes; i.e. Duplicates, Too Broad, Need MRE, Not About Programming, etc.) in a specific tag. I want to do this for the questions that are already closed and have not been reopened (if possible). So far, I just managed to get the list of closed questions in a tag:

https://data.stackexchange.com/stackoverflow/query/1751682/closed-questions-in-a-specific-tag?TagName=r

SELECT p.Title,
       p.Id AS [Post Link]
  FROM Posts As p
  Inner Join PostTags As pt
    ON p.ID = pt.PostId
  Inner Join Tags As t
    ON t.Id = pt.TagId
WHERE p.ClosedDate IS NOT NULL AND
      t.TagName = '##TagName##'

How can I get a tally of the different close reasons by UserIDs for a specific tag?

3
  • 2
    this is for the close voters: meta.stackexchange.com/questions/242618/…
    – rene
    May 16, 2023 at 21:13
  • @rene this only works for the questions that are not deleted, right? data.stackexchange.com/stackoverflow/query/1751782/… Any way to get it for all of the questions, whether deleted or not deleted? Also this gives me the user link, so upon downloading the CSV it will be converted to numbers :/ p.s. I definitely messed up something. The counts don't look right.
    – M--
    May 16, 2023 at 22:30
  • 1
    yeah, posthistory doesn't have deleted posts.
    – rene
    May 17, 2023 at 10:08

2 Answers 2

6

You can only obtain this information from SEDE for posts that are closed and not deleted. The close voters are stored in the text column of the PostHistory table for rows where PosthistoryTypeId = 10. In the comment column the by the system final selected close reason is stored. So this is the majority reason, not necessarily the close reason all voters selected. I've pivotted the result over all closed reasons, both current and historic.

The text column holds a Json with the following structure:

{
    "OriginalQuestionIds": [14793922],
    "Voters": [{
            "Id": 964243,
            "DisplayName": "Boann"
        }, {
            "Id": 308825,
            "DisplayName": "Maerlyn",
            "BindingReason": {
                "GoldTagBadge": "php"
            }
        }
    ]
}

Most relevant in that Json is the Voters array as it holds for each voter:

  • Id (the userid of the voter, not present when user is deleted)
  • Displayname (the display name of the user)
  • BindingReason (present on duplicate close votes, indicates for what reason/tag the duplicate was closed)

In the query you can see I let SQL Server convert that Json to rows with the objects found in the Voter array by doing:

cross apply openjson(text, '$.Voters')

so I can grab the Id that references the User with

json_value(value, '$.Id'))

or alternatively use a with (VoterId int '$.Id' ) clause on the OpenJson statement as demonstrated in this query by user Martin.

This is the query I came up with:

;with taggedPosts as 
(
  Select pt.PostId
  from PostTags As pt
  Inner Join Tags As t
    ON t.Id = pt.TagId
  WHERE t.TagName = '##TagName##'
)
, closevoters as
(
select convert(int,json_value(voters.value , '$.Id')) as voterid
     , ph.comment
from posthistory ph
inner join taggedPosts tp on tp.postid = ph.postid
cross apply openjson(ph.text, '$.Voters') voters
where posthistorytypeid = 10 -- close event
)

select voterid as [User Link]
     , [1]+[2]+[3]+[4]+[7]+[10]+[20]+[101]+[102]+[103]+[104]+[105]+[199]+[201]+[202] total
     , [101] [Duplicate of...],[102] [Off-topic],[103] [Needs details or clarity],[104] [Needs more focus],[105] [Opinion-based],[1] [exact duplicate],[2] [off topic],[3] [not constructive],[4] [not a real question],[7] [too localized],[10] [general reference],[20] [noise or pointless], [199] [NotResolved],[201] [off-topic (channels)],[202] [Obsolete] 
from (
select voterid
     , comment
from closevoters
)  data
pivot
( 
   count(comment)
  FOR comment in ([101], [102], [103], [104], [105], [199], [201], [202], [1], [2], [3], [4], [7], [10], [20] )
) pvt
order by 2 desc

And this is what the result looks like today for the R tag:

result count of voters and their selected close reason

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

2

You can't; SEDE provides the final close reason, the voters and the duplicate target:

enter image description here

but not who voted for which close reason. Not even the UI shows who voted for what reason (though auto-generated comments might give a clue sometimes).

If you're only interested in the "number of close votes in a specific tag" - that is certainly possible, if you know how to parse JSON in SQL Server, and accept the fact that close voters are only visible if the question was actually closed (at some point in time).

3
  • That is actually fine. I am ok to get the list for the final closed reason, regardless whether that user voted for that specific type. Honestly, I am more interested in differentiating between dupe-tagging and other type of CVs. I know that is not the most reliable data to work with, but I just want to get a list of top participants in moderation (in the form of CVs) for a specific tag. I am fine to live with the caveat that you mentioned above.
    – M--
    May 16, 2023 at 20:52
  • 2
    That should be doable, but parsing JSON with SQL Server isn't common practice for me, and it's almost bedtime here ... if you need help with that, either wait for a reply in the SEDE chatroom or wait for a more specific answer here :)
    – Glorfindel Mod
    May 16, 2023 at 20:56
  • 1
    Thank you. I am in no rush. I just tagged you guys in the chatroom because I wanted to make sure that I get help from the bests :)
    – M--
    May 16, 2023 at 20:57

You must log in to answer this question.

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