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), (3), (4), (811), (1438), (7028), (17174), (23354), (105971), (115866), (122607)

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)
  -- Skip 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 moderator actions
  AND ph.UserId NOT IN (SELECT UserId FROM #mods)
  AND ph.CreationDate >= '2008-01-01'
  AND ph.CreationDate <  '2014-01-01'
ORDER BY
  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