0

Please login or register to vote for this query.

(click on this box to dismiss)

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.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 (1~9), deletion (12, 13), locking (14, 15), protection (19, 20)
  AND ph.PostHistoryTypeId NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 12, 13, 14, 15, 19, 20)
  -- 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)
      )
    )
  -- Before implementation of the dupehammer, for simplicity
  AND ph.CreationDate < '2014-05-01'
ORDER BY
  ph.UserId DESC,
  ph.CreationDate DESC

fork query

edited jan 2 15

created jan 2 15 senshin