1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in the history and origins of science and mathematics

SELECT
p.Id, 
p.Tags,
t.TagName,
CASE
  WHEN p.Title LIKE '%' + t.TagName + '%' THEN 'In Title'
  WHEN p.Body LIKE '%' + t.TagName + '%' THEN 'In Question'
  ELSE NULL
END AS [Tag Location],
p.CreationDate

FROM Posts AS p

INNER JOIN PostTypes as ptypes
  ON p.PostTypeId = ptypes.Id
  AND ptypes.Name = 'Question'
  
INNER JOIN PostTags AS ptags
  ON p.Id = ptags.PostId
  
INNER JOIN Tags AS t
  ON t.Id = ptags.TagId
  
INNER JOIN -- ONLY WANT THE TOP 10 MOST POPULAR TAGS ON S.O.
  (
  SELECT TOP 10 TagName
  FROM Tags
  ORDER BY Count DESC
  ) as top_tags
   ON t.TagName = top_tags.Tagname
  
WHERE (p.Title LIKE '%' + t.TagName + '%'
       OR p.Body LIKE '%' + t.TagName + '%')
AND p.CreationDate between '2020-09-01' and '2020-09-30'

ORDER BY p.Id

fork query

edited oct 25

created oct 23 Dean.Power