1

Please login or register to vote for this query.

(click on this box to dismiss)

The proliferation of tags can mean that many questions have no popular tags. Such questions are more difficult to find, and do not contribute to enabling the "Generalist" badge for smaller sites. Here, choose a threshold (i.e., 40, for top 40 tags), and this query will return all questions that have no tags that popular. It will also provide the name of the question's most popular tag. Related: Query 342344 (Which open or duplicate questions have no popular tags?)

Q&A for committed Christians, experts in Christianity and those interested in learning more

DECLARE @Threshold int = ##Threshold?40##;
-- Threshold: Top tags threshold:   "Returns all questions that have no tags in the top N tags"

--Calculate the number of questions that have the Nth largest tag
DECLARE @Questions int 
SET @Questions = (SELECT TOP 1 Count
FROM (
SELECT TOP (@Threshold) Count
FROM Tags
ORDER BY Count DESC) a
ORDER BY Count);

WITH Question_Tags AS
(
select t.Id AS Tag_ID, t.TagName AS Tag_Name,
t.Count AS Count, p.Id AS Post_ID, p.Title AS Post_Title,
p.PostTypeId AS Type, p.OwnerUserID AS Owner,
max(t.Count) over (partition by p.Id) AS Max_Count
from Tags t
INNER JOIN PostTags pt on t.Id = pt.TagId 
INNER JOIN Posts p on pt.PostId = p.Id 
)
SELECT 
'site://questions/' + CAST(Post_ID AS nvarchar) + '|' + Post_Title AS Question,
'site://questions/tagged/' + CAST(Tag_Name AS nvarchar) + '|' + Tag_Name AS 'Most Popular Tag Name',
Max_Count AS 'Tag''s Number of Questions'
FROM Question_Tags
where Type = 1
AND Max_Count = Count -- Shows each question's most popular tag
AND Max_Count < @Questions 
ORDER BY Max_Count

fork query

edited aug 3 15

created jul 31 15 Nathaniel