Most frequent tag "cleaners"

1

Please login or register to vote for this query.

(click on this box to dismiss)

MathOverflow

Q&A for professional mathematicians

---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
), users_cte AS (
SELECT
  t.tagname AS tagname,
  ph.UserId AS uid,
  ph.UserDisplayName AS udn
FROM tagsfirst_cte t INNER JOIN PostHistory ph ON ph.id=t.firstid
)

SELECT
  COUNT(tagname) AS Count,
  uid AS [User Link],
  udn AS [Display Name]
FROM users_cte
GROUP BY uid, udn
ORDER BY COUNT(tagname) DESC

Enter Parameters

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