Query for the usage of a tag over time

Question

Maybe a query like this already exists. I am looking for a query that accepts more than one tag, for example, java, sql and vb, and then tracks their progress over time. It would be useful to see which tags are dwindling and which tags are more popular in SE.

Answer 1

The following query shows the number of questions with specific tags across all sites in the SE network.

-- start create url from dbname
IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #siteurl;
GO
create procedure #siteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      -- we have three sites that have borked DNS entries
      set @dbname = (case @dbname
      when 'StackExchange.Audio' then 'StackExchange.Avp'
      when 'StackExchange.Audio.Meta' then 'StackExchange.Avp.Meta'
      when 'StackExchange.Mathoverflow.Meta' then 'net.Mathoverflow.meta'
      else @dbname
      end)
      -- and one of those doesn't want to end with .com
      if @dbname <> 'net.Mathoverflow.meta' 
         set @dbname = 'com.' + @dbname
      exec #recursesiteurl @dbname,  @res OUTPUT
   end
GO
-- create url from dbname
IF OBJECT_ID ( '#recursesiteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #recursesiteurl;
GO
create procedure #recursesiteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      declare @pos integer
      declare @rec nvarchar(250)
       
      set @res = @dbname
      set @pos = CHARINDEX('.', @dbname)
      if (@pos > 0 ) 
      begin
         set @rec = substring(@dbname, @pos+1, len(@dbname))
         exec #recursesiteurl @rec,  @rec output
         set @res = @rec 
                  + '.' 
                  + substring(@dbname, 0, @pos) 
      end
   end;
GO

declare @tag nvarchar(25) = ##tag:string##  -- tag1
declare @tag2 nvarchar(25) = ##tag2:string##  -- tag2
declare @tag3 nvarchar(25) = ##tag3:string##  -- tag3

-- all databases
declare  db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 5 -- skip master, temp, model, msdb, Data.SE

declare @db_c_name sysname   -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string

-- result table
create table #all_tags ( site nvarchar(250)
                       , creationdate datetime
                       , tagcount int
                       , tagname nvarchar(200));
                            
open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
begin
    set @sql = N'use '+ QUOTENAME(@db_c_name) +';
               declare @url nvarchar(250)
               exec #siteurl ''' + @db_c_name  + ''', @url output
               insert into #all_tags 
               select @url
               , cast(p.creationdate as date)
               , count(*) as tagcount
               , tagname
               from posts p 
               inner join posttags pt on pt.postid = p.id
               inner join tags t on t.id = pt.tagid 
               where t.tagname in ('''+ @tag + ''','''+ @tag2 + ''','''+ @tag3 + ''')
               group by cast(p.creationdate as date)
               , t.tagname
               ;'
    exec (@sql)
   fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;

select /* replace(
       replace(
       replace(site,
         '.StackExchange.com',''),
         '.com',''),
         '.net','') as [site]
         ,*/ dateadd(d, -datepart(dw, creationdate), creationdate)
     , tagname
     , sum(tagcount)
from #all_tags
group by dateadd(d, -datepart(dw, creationdate), creationdate)
, tagname
order by dateadd(d, -datepart(dw, creationdate), creationdate)
, tagname

drop table #all_tags

drop procedure #recursesiteurl
drop procedure #siteurl

The query doesn't take into account when tags are added later. It simply assumes that the creationdate of the post is also the first time the tag was used. Bringing that logic into the query would probably make it timeout and I doubt if the results would differ very much.

When run with the three example tags you provided the output looks like this: