Tags cleaned-up by a specific user

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Apps

Q&A for apps, scripts, and development with the Stack Exchange API

---the query looks at the last revision with the given tag
---and than the revision after that
---mostly that could correspond to "tag cleaner"
---in some cases the post where the tag was created might have been deleted
; with tagshistory_cte AS
(
SELECT
  Id AS phid,
  replace(value,'>','') TagName
FROM
  PostHistory
  CROSS APPLY STRING_SPLIT(Text,'<')
WHERE PostHistoryTypeId in (3,6,9)
and len(value) > 0
), tagslast_cte AS (
SELECT
  tagname,
  MAX(phid) AS lastid
FROM tagshistory_cte
GROUP BY tagname
), emptytags_cte AS(
SELECT
  tl.tagname AS tagname,
  tl.lastid AS lastid
  FROM tagslast_cte tl LEFT OUTER JOIN Tags t ON tl.tagname=t.TagName
  WHERE (t.Tagname IS NULL)
), emptytagspid_cte AS (
SELECT
  e.tagname AS tagname,
  e.lastid AS lastid,
  ph.postid AS pid
FROM emptytags_cte e INNER JOIN PostHistory ph ON ph.id=e.lastid
), tagsfirst_cte AS (
SELECT
  e.tagname AS tagname,
  MIN(ph.Id) AS firstid
FROM emptytagspid_cte e INNER JOIN PostHistory ph ON ph.PostId=e.pid
  WHERE (ph.PostHistoryTypeId in (3,6,9)) AND (ph.Id>e.lastid)
GROUP BY e.tagname
)

SELECT
  t.tagname,
  ph.CreationDate,
  ph.UserId AS [User Link], 
  url = 'site://posts/' + CAST(ph.PostId AS varchar)+'/revisions',
  ph.PostId AS [Post Link],
  ph.Comment
FROM tagsfirst_cte t INNER JOIN PostHistory ph ON ph.id=t.firstid
WHERE ph.UserId=##UserId##

Enter Parameters

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