How can one see how many regular users cast at least one reopen vote during the current year?

Question

I am looking at some question reopening statistics. How can one see how many regular users cast at least one reopen vote during the current year? Preferably with the count of how many reopen votes each of them cast during that period.

Answer 1

As far as I can tell the best we can do is look for reopen events and isolate those users who contributed to the reopening. These most votes, including reopen votes, are typically private and purged from the Votes table in SEDE. Additionally, PostHistory does not contain records for posts that have been deleted. For this reason, SEDE is highly limited and will only produce a very small subset of events.

Requirements to see the reopen vote in SEDE:

• The post must be currently (as of the last SEDE backup) be visible (undeleted).
• The post must have been successfully reopened (no pending reopen votes can be considered)

This query does not represent a total number of reopen votes. This will be a small subset of actual reopen votes cast, but is the best we can do with the public record available.

SELECT
    voters.Id AS [User Link],
    COUNT(*) AS [Count]
FROM
    PostHistory ph
        CROSS APPLY OPENJSON(ph.Text, '$.Voters') WITH (Id INT '$.Id') voters
WHERE
    PostHistoryTypeId = 11     -- Reopen Event
        AND
    YEAR(CreationDate) = YEAR(GETDATE()) -- Reopen event happened in current year (not necessarily the vote)
GROUP BY voters.Id
ORDER BY [Count] DESC;

---

Note there is also no way to determine current or former Diamond holders (either for staff or site moderators) which makes it somewhat difficult to exclude these users. Which makes isolating only "Regular Users" difficult. Genuinely the most straightforward approach would be to exclude users manually...

Here's an example where I manually went through the list of users in the 2023 results and manually the user IDs of diamond holders (for any part of the year) to the exclude list.

CREATE TABLE #UserIdsToExclude
(
    UserId INT NOT NULL
);

INSERT INTO
    #UserIdsToExclude
VALUES
    (59303),  -- ChrisF (Site Mod)
    (295232), -- Glorfindel (Site Mod)
    (369802), -- Tinkeringbell (Site Mod)
    (209031), -- KyleMit (Staff-Mod)
    (164394), -- Connell (Staff-Mod)
    (135565), -- Journeyman Geek (Former Site Mod)
    (284336), -- Catija (Former Staff-Mod)
    (335251)  -- V2Blast (Former Staff-Mod)
;


SELECT
    voters.Id AS [User Link],
    COUNT(*) AS [Count]
FROM
    PostHistory ph
        CROSS APPLY OPENJSON(ph.Text, '$.Voters') WITH (Id INT '$.Id') voters
WHERE
    PostHistoryTypeId = 11 AND
    YEAR(CreationDate) = YEAR(GETDATE()) AND
    voters.Id NOT IN (
        SELECT
            UserId
        FROM
            #UserIdsToExclude
        )
GROUP BY voters.Id
ORDER BY [Count] DESC;

Unfortunately, the PostHistory record does not include an information about when a unilateral privilege was used to reopen the question so there's no way to differentiate where their reopen vote functioned as a unilateral reopen. (Yes, it would be possible to check where the total number of reopen votes was less than expected, but that would also exclude regular users with gold tag badges which may or may not be the desired use case here)

---

I've also put together a slightly more general query which can be used to find reopen votes from a specified year and exclude arbitrary users. Like, for example, 2022 excluding site mods.

This query will also complete on Stack Overflow so it's likely that this can be run on any site on the network.