For the list of non-series tags, see: https://data.stackexchange.com/anime/query/1863699.
Q&A for expert genealogists and people interested in genealogy or family history
-- TagType: Exclude tags by types, e.g. 'regions', 'general'; input '' to include all. -- TagName: Exclude tags by names, e.g. 'anime-production'; input '' to include all. -- SearchBody: Also search tag keywords in question bodies; on/off: 1/0. CREATE TABLE #NonSeriesTags ( TagType nvarchar(50) , TagName nvarchar(35) COLLATE SQL_Latin1_General_CP1_CS_AS ) INSERT INTO #NonSeriesTags VALUES ('characters', 'domo-kun') , ('characters', 'hatsune-miku') , ('characters', 'os-tan') , ('characters', 'super-sonico') , ('companies', 'anime-planet') , ('companies', 'crunchyroll') , ('companies', 'funimation') , ('companies', 'gainax') , ('companies', 'key') , ('companies', 'kyoto-animation') , ('companies', 'madhouse') , ('companies', 'myanimelist') , ('companies', 'netflix') , ('companies', 'pixiv') , ('companies', 'sentai-filmworks') , ('companies', 'studio-ghibli') , ('companies', 'type-moon') , ('conventions', 'anime-expo') , ('conventions', 'comiket') , ('fandom', 'amv') , ('fandom', 'conventions') , ('fandom', 'cosplay') , ('fandom', 'doujinshi') , ('fandom', 'itasha') , ('fandom', 'merchandise') , ('general', 'crossovers') , ('general', 'culture') , ('general', 'japanese-language') , ('general', 'light-novel-site') , ('general', 'meme') , ('general', 'music') , ('general', 'panel-arrangement') , ('general', 'quotes') , ('general', 'resources') , ('general', 'sign-language') , ('general', 'streaming-sites') , ('general', 'symbolism') , ('general', 'terminology') , ('general', 'theme-song') , ('general', 'tourism') , ('general', 'tropes') , ('industries', 'anime-history') , ('industries', 'anime-industry') , ('industries', 'anime-production') , ('industries', 'censorship') , ('industries', 'copyright') , ('industries', 'events') , ('industries', 'game-history') , ('industries', 'law') , ('industries', 'licensing') , ('industries', 'light-novel-history') , ('industries', 'light-novel-production') , ('industries', 'live-action-production') , ('industries', 'localization') , ('industries', 'mangaka') , ('industries', 'manga-history') , ('industries', 'manga-production') , ('industries', 'promotional-videos') , ('industries', 'visual-novel-production') , ('industries', 'voice-acting') , ('other-reified', 'anime-no-chikara') , ('other-reified', 'vocaloid') , ('people', 'aimer') , ('people', 'barasui') , ('people', 'claris') , ('people', 'hayao-miyazaki') , ('people', 'hideaki-anno') , ('people', 'hiroyuki-sawano') , ('people', 'jun-maeda') , ('people', 'kajiura-yuki') , ('people', 'kazuki-nakashima') , ('people', 'kazuki-takahashi') , ('people', 'ken-akamatsu') , ('people', 'kouji-seo') , ('people', 'maaya-sakamoto') , ('people', 'makoto-shinkai') , ('people', 'mamiko-noto') , ('people', 'mamoru-hosoda') , ('people', 'mamoru-oshii') , ('people', 'masamune-shirow') , ('people', 'sachika-misawa') , ('people', 'shun-saeki') , ('publications', 'ribon') , ('publications', 'shonen-jump') , ('regions', 'australia') , ('regions', 'england') , ('regions', 'europe') , ('regions', 'germany') , ('regions', 'greece') , ('regions', 'india') , ('regions', 'japan') , ('regions', 'north-korea') , ('regions', 'singapore') , ('regions', 'spain') , ('regions', 'united-states') , ('special', 'identification-request') , ('special', 'retag') ; WITH NonSeriesTags AS ( SELECT #NonSeriesTags.TagType , #NonSeriesTags.TagName , TagSynonyms.SourceTagName FROM #NonSeriesTags LEFT JOIN TagSynonyms ON #NonSeriesTags.TagName = TagSynonyms.TargetTagName ) SELECT DENSE_RANK() OVER (ORDER BY Posts.Id) AS [#] , Posts.Id AS [Post Link] , Posts.Tags , NonSeriesTags.TagName , STRING_AGG(NonSeriesTags.SourceTagName, ', ') WITHIN GROUP (ORDER BY NonSeriesTags.SourceTagName) AS [TagSynonyms] , COUNT(NonSeriesTags.TagName) OVER (PARTITION BY NonSeriesTags.TagName ORDER BY NonSeriesTags.TagName) AS TagSuggestedCount , Tags.[Count] AS [QuestionsTagged] FROM Posts INNER JOIN NonSeriesTags ON Posts.PostTypeId = 1 AND NonSeriesTags.TagType NOT IN (##TagType##) AND NonSeriesTags.TagName NOT IN (##TagName##) AND Posts.Tags NOT LIKE '%<' + NonSeriesTags.TagName + '>%' AND ( LOWER(Posts.Title) LIKE '%[^a-zA-Z]' + REPLACE(NonSeriesTags.TagName, '-', ' ') + '[^a-zA-Z]%' OR LOWER(Posts.Body) LIKE '%[^a-zA-Z]' + REPLACE(NonSeriesTags.TagName, '-', ' ') + '[^a-zA-Z]%' OR ( ##SearchBody:int?1## = 1 AND NonSeriesTags.SourceTagName IS NOT NULL AND LOWER(Posts.Title) LIKE '%[^a-zA-Z]' + REPLACE(NonSeriesTags.SourceTagName, '-', ' ') + '[^a-zA-Z]%' ) OR ( ##SearchBody:int?1## = 1 AND NonSeriesTags.SourceTagName IS NOT NULL AND LOWER(Posts.Body) LIKE '%[^a-zA-Z]' + REPLACE(NonSeriesTags.SourceTagName, '-', ' ') + '[^a-zA-Z]%' ) ) INNER JOIN Tags ON NonSeriesTags.TagName = Tags.TagName GROUP BY Posts.Id , Posts.Tags , NonSeriesTags.TagName , Tags.[Count] ORDER BY Posts.Id , NonSeriesTags.TagName