Identification tags

0

Please login or register to vote for this query.

(click on this box to dismiss)

Lots of sites have identification tags. The results here have a few false positives, and some of the tags look like they aren't endorsed by their community. Usage: This will find tags on all sites, regardless of which site you run it on. Bug: the links returned by the query don't always work.

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

DECLARE @command NVARCHAR(1024) = 'USE [?];
IF ''?'' NOT LIKE ''%meta%''
AND ''?'' <> ''StackApps''
AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''Tags'')
INSERT INTO #Tags
SELECT replace(''?'', ''StackExchange.'', '''') AS Site,
       Tags.TagName,
       Tags.Count,
       round(avg(Posts.Score*1.0),3),
       round(100.0*count(closed.Id)/Tags.Count,3)
FROM dbo.Tags
inner join dbo.PostTags on PostTags.TagId = Tags.Id
inner join dbo.Posts on Posts.Id = PostTags.PostId
left join dbo.Posts closed on closed.Id = PostTags.PostId
                       and closed.ClosedDate is not null
WHERE TagName like ''%identification%'' or TagName like ''identify-%''
GROUP BY Tags.TagName, Tags.Count;'

CREATE TABLE #Tags (Site NVARCHAR(255), Tag NVARCHAR(35), Count INT,
                    [Average Score] float, [Percent closed] float);
EXEC sp_msforeachdb @command

SELECT Site,
       'http://' + Site + '.stackexchange.com/tags/' + Tag + '|' + Tag as Tag,
       Count,
       [Average Score],
       [Percent Closed]
FROM #Tags
ORDER BY Count desc, Site, Tag

Enter Parameters

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