Finds every Star Trek-related post on scifi.SE that is missing the [star-trek] tag. This relies on the question having at least one other Star Trek tag. Star Trek tags were identified using this query: http://data.stackexchange.com/scifi/query/75877/related-tags.
Q&A for community managers, administrators, and moderators
-- MatchEdgeCases: Match edge cases "0=false, 1=true, 2=only edge cases" declare @match_edge_cases int = ##MatchEdgeCases:int?0## -- StarTrekTags: Match star-trek-* "0=false, 1=true" declare @star_trek_tags int = ##StarTrekTags:int?1## -- Make a table of matching posts select Id, Title, Tags, OwnerUserId, OwnerDisplayName, CreationDate, AnswerCount into #StarTrek from Posts where PostTypeId = 1 and Tags not like '%<star-trek>%' -- exclude people who want to opt out of this retagging --and Posts.OwnerUserId not in (1234) --and not exists (select Id from Posts a where a.ParentId = Posts.Id and a.OwnerUserId in (2242)) and (@star_trek_tags >= 1 or (@star_trek_tags = 0 and Tags not like '%<star-trek-tos>%' and Tags not like '%<star-trek-tng>%' and Tags not like '%<star-trek-ds9>%' and Tags not like '%<star-trek-voyager>%' and Tags not like '%<star-trek-enterprise>%')) --and Tags not like '%<star-trek-%>%')) and ( (@match_edge_cases < 2 and -- thankfully, a lot of these tags start with star-trek: (Tags like '%<star-trek-%>%' -- films: or Tags like '%<the-final-frontier>%' or Tags like '%<the-search-for-spock>%' or Tags like '%<the-undiscovered-country>%' or Tags like '%<the-voyage-home>%' or Tags like '%<the-wrath-of-khan>%' -- miscellaneous terms: or Tags like '%<federation>%' or Tags like '%<holodeck>%' or Tags like '%<kobayashi-maru>%' or Tags like '%<lcars>%' or Tags like '%<mirror-universe>%' or Tags like '%<phaser>%' or Tags like '%<praxis>%' or Tags like '%<prime-directive>%' or Tags like '%<stardate>%' or Tags like '%<starfleet-%>%' or Tags like '%<universal-translator>%' or Tags like '%<uss-enterprise>%' or Tags like '%<grand-nagus>%' -- species: or Tags like '%<andorian>%' or Tags like '%<betazoid>%' or Tags like '%<borg>%' or Tags like '%<changeling>%' or Tags like '%<ferengi>%' or Tags like '%<jemhadar>%' or Tags like '%<klingon>%' or Tags like '%<romulan>%' or Tags like '%<vulcan>%' or Tags like '%<xindi>%' -- characters: or Tags like '%<barclay>%' or Tags like '%<captain-kirk>%' or Tags like '%<jean-luc-picard>%' or Tags like '%<riker>%' or Tags like '%<seven-of-nine>%' or Tags like '%<spock>%' or Tags like '%<worf>%')) -- Not necessarily specific to Star Trek: or (@match_edge_cases > 0 and (Tags like '%<cloaking>%' or Tags like '%<impulse>%' or Tags like '%<klingon-language>%' or Tags like '%<replicator>%' or Tags like '%<transporter>%' or Tags like '%<transwarp>%' or Tags like '%<warp>%' )) ) -- Display the results in a table select #StarTrek.Id as [Post Link], Tags, (case when OwnerUserId is not null then 'site://users/' + cast(OwnerUserId as nvarchar) + '|' else '' end) + coalesce(OwnerDisplayName, Users.DisplayName) as [User Link], #StarTrek.CreationDate, AnswerCount as Answers from #StarTrek left join Users on OwnerUserId = Users.Id order by CreationDate desc --Tags, Title -- Make a graph of the results -- The right join selects every month since the creation of the site -- (assuming that there is at least one post per month) select Month, count(Id) as Count from #StarTrek right join (select distinct datefromparts(year(CreationDate), month(CreationDate), 1) as Month from Posts) as Dates on month(CreationDate) = month(Month) and year(CreationDate) = year(Month) group by Month order by Month desc select getdate() as [Time of query]