Unilaterally Closed Posts

0

Please login or register to vote for this query.

(click on this box to dismiss)

Finding posts which were closed by a single mod vote: https://interpersonal.meta.stackexchange.com/a/3950/3171 With thanks to https://data.stackexchange.com/workplace/query/974550/how-to-use-data-explorer-to-find-the-most-prolific-closers-on-a-site

GIS

Q&A for cartographers, geographers and GIS professionals

;WITH singleclosevotedposts AS
(
SELECT
postid,
COUNT(voter) as votercount,
text

FROM
(
SELECT
ph.postid,
ph.text,
ind.value,
CONVERT(int,json_value( 
        ph.text, concat(
          '$.Voters[', 
          ind.value, 
          '].Id')
          )) AS voter
FROM posthistory ph
CROSS JOIN string_split('0,1,2,3,4,5,6', ',') ind
WHERE ph.posthistorytypeid = 10 -- close event
) closevoters

GROUP BY postid,text
HAVING COUNT(voter)=1
)

SELECT
cv.postid AS [Post Link],
CONVERT(int,json_value( 
        cv.text, concat(
          '$.Voters[', 
          0, 
          '].Id')
          )) AS [User Link],
posts.creationdate,
posts.closeddate, -- null means it was since reopened
posts.lastactivitydate,
posts.answercount,
posts.commentcount

FROM singleclosevotedposts cv
LEFT JOIN posts ON cv.postid=posts.id
ORDER BY posts.lastactivitydate DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results