Mysterious closures by a single non-moderator

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

CREATE TABLE #mods (UserId int)
INSERT INTO #mods SELECT UserId FROM Badges WHERE Name IN ('Sheriff', 'Constable')
INSERT INTO #mods VALUES (-1)
-- For Stack Overflow
INSERT INTO #mods VALUES 
(1), (2), (3), (4), 
(51),
(811), 
(1438), (1583), (3279), (6212), (7028), 
(10936), (13249), (17174), (23354), (50049), (70613), (80572), (91687), (98786),
(105971), (115049), (115866), (122607), (178082), (256196), (274402), (347414), (426671), (472021), (716216), (850152),
(1370722)

DECLARE @findstr nvarchar(max) = '"Id":'

SELECT 
  ph.Id, 
  pht.Name,
  ph.PostId AS [Post Link], 
  ph.CreationDate, 
  ph.UserId AS [User Link], 
  ph.Comment, 
  ph.Text 
FROM 
  PostHistory AS ph
  LEFT OUTER JOIN Posts AS p
    ON ph.PostId = p.Id
  LEFT OUTER JOIN PostHistoryTypes AS pht
    ON ph.PostHistoryTypeId = pht.Id
WHERE
  LEN(ph.Text) - LEN(REPLACE(ph.Text, @findstr, '')) = 1*LEN(@findstr)
  -- Exclude editing-related things
  AND ph.PostHistoryTypeId NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
  -- Exclude protection
  AND ph.PostHistoryTypeId NOT IN (19, 20)
  -- Exclude self-deletion
  AND NOT (ph.PostHistoryTypeId IN (12, 13) AND p.OwnerUserId = ph.UserId)
  -- Exclude any deletion whatsoever
  AND ph.PostHistoryTypeId NOT IN (12, 13)
  -- Exclude moderator actions and standard Community actions
  AND (
    ph.UserId NOT IN (SELECT UserId FROM #mods) 
    OR (
      ph.UserId = -1 
      AND ph.Text != '{"Voters":[{"Id":-1,"DisplayName":"Community"}]}'
      AND CAST(SUBSTRING(ph.Text, CHARINDEX(@findstr, ph.Text) + LEN(@findstr), CHARINDEX(',', ph.Text, CHARINDEX(@findstr, ph.Text) + LEN(@findstr)) - (CHARINDEX(@findstr, ph.Text) + LEN(@findstr))) AS int) NOT IN (SELECT UserId FROM #mods)
      )
    )
  AND ph.CreationDate >= '2008-01-01'
  AND ph.CreationDate <  '2014-05-01' -- before implementation of the dupehammer
ORDER BY
  ph.UserId DESC,
  ph.CreationDate DESC
  
  
-- 12 = Post Deleted
-- 13 = Post Undeleted
-- 19 = Question Protected
-- 20 = Question Unprotected

Enter Parameters

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