1

Please login or register to vote for this query.

(click on this box to dismiss)

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]

fork query

edited jun 1 17 amaranth

created feb 9 12 Rebecca Chernoff