Questions potentially missing some non-series tags

1

Please login or register to vote for this query.

(click on this box to dismiss)

For the list of non-series tags, see: https://data.stackexchange.com/anime/query/1863699.

Engineering

Q&A for professionals and students of engineering

-- 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

Enter Parameters

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