Most frequent tags on spam posts

1

Please login or register to vote for this query.

(click on this box to dismiss)

Constructed Languages

Q&A for designers and users of artificial or invented languages, consciously developed instead of having developed naturally

; with spam_cte AS
(
SELECT
  p.Id AS pid,
  p.Tags AS Tags
  FROM PostsWithDeleted p INNER JOIN Votes v ON v.PostId=p.Id
  WHERE (v.VoteTypeId IN (##votetype?4,12##))
---4 = Offensive
---12 = Spam
---4,12 = both
  AND (p.PostTypeId=1)
---only questions have tags  
), tags_cte AS
(SELECT
  pid,
  replace(value,'>','') TagName
FROM
  spam_cte
  CROSS APPLY STRING_SPLIT(Tags,'<')
  WHERE len(value) > 0
)

SELECT
  TagName,
  COUNT(pid) AS QuestionCount
FROM tags_cte
GROUP BY TagName
ORDER BY COUNT(pid) DESC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results