1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Drupal developers and administrators

---the query looks at the first revision with the given tag
---mostly that could correspond to tag creator
---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
), tagsfirst_cte AS (
SELECT
  tagname,
  MIN(phid) AS firstid
FROM tagshistory_cte
GROUP BY tagname
)

SELECT TOP(##num?100##)
  COUNT(t.tagname) AS [Count],
  url = 'site://posts/' + CAST(ph.PostId AS varchar)+'/revisions',
  ph.PostId AS [Post Link],
  ph.CreationDate,
  ph.UserId AS [User Link],
  ph.UserDisplayName
FROM tagsfirst_cte t INNER JOIN PostHistory ph ON ph.id=t.firstid
WHERE (ph.CreationDate>='##Date?2010-01-01##')
---We might be interested mainly in newer posts - after the tag system already has some stability, creating several tags in the same post is a bit unusual.
GROUP BY ph.PostId, ph.CreationDate, ph.UserId, ph.UserDisplayName
ORDER BY COUNT(t.Tagname) DESC

fork query

edited nov 17

created nov 17 Martin20528