0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for music historians, critics, and fans

WITH pre_closings AS (
select close_events.*,
(SELECT MIN(edit_events.CreationDate) FROM PostHistory as edit_events
  WHERE edit_events.PostId = close_events.PostId
  AND edit_events.PostHistoryTypeId = 5
  AND edit_events.CreationDate > close_events.CreationDate) next_edit,
(SELECT MIN(reopen_events.CreationDate) FROM PostHistory as reopen_events
  WHERE reopen_events.PostId = close_events.PostId
  AND reopen_events.PostHistoryTypeId = 11
  AND reopen_events.CreationDate > close_events.CreationDate) next_reopen,
JSON_QUERY(close_events.Text, '$.Voters') AS Voters
from PostHistory as close_events
WHERE close_events.PostHistoryTypeId = 10
and close_events.CreationDate >= DATEFROMPARTS(2024,1,1)
),
closings AS (
SELECT
*,
( CASE WHEN (
  (next_reopen IS NOT NULL)
  AND
  ((next_edit IS NULL) OR next_edit > next_reopen)
) THEN 'boo' 
    ELSE NULL
    END ) AS reopens
FROM pre_closings
),
close_votes AS (
SELECT
  *
FROM closings
CROSS APPLY OPENJSON(closings.voters) WITH (
   [DisplayName] nvarchar(max) '$.DisplayName'
) AS cvs
)
SELECT
close_votes.DisplayName as user_name,
COUNT(*) as close_votes,
COUNT(ALL reopens) AS overturned_close_votes,
FORMAT(
  (CAST(COUNT(ALL reopens) as float) / CAST(COUNT(*) as float)),
  'P'
  ) AS badness
FROM close_votes
GROUP BY close_votes.DisplayName
ORDER BY COUNT(ALL reopens) DESC

fork query

created sep 17 alphabet