1

Please login or register to vote for this query.

(click on this box to dismiss)

TODO: 1. normalize for growth of SO itself 2. estimate average growth rate

Q&A for professional and enthusiast programmers

-- Monthly growth of given tag
-- TODO:
-- 1. normalize for growth of SO itself
-- 2. estimate average growth rate

DECLARE @tag nvarchar(25) = ##tag:string##

create table #Dates
(
 Year int,
 Month int,
)

insert into #Dates (Year, Month)
select 2008, 7 union all
select 2008, 8 union all 
select 2008, 9 union all
select 2008, 10 union all
select 2008, 11 union all
select 2008, 12 union all
select 2009, 1 union all
select 2009, 2 union all
select 2009, 3 union all
select 2009, 4 union all
select 2009, 5 union all
select 2009, 6 union all
select 2009, 7 union all
select 2009, 8 union all
select 2009, 9 union all
select 2009, 10 union all
select 2009, 11 union all
select 2009, 12 union all
select 2010, 1 union all
select 2010, 2 union all
select 2010, 3 union all
select 2010, 4 union all
select 2010, 5 union all
select 2010, 6 union all
select 2010, 7 union all
select 2010, 8 union all
select 2010, 9 union all
select 2010, 10 union all
select 2010, 11 union all
select 2010, 12 union all
select 2011, 1
-- union all
-- select 2011, 2

select
  #Dates.Year,
  #Dates.Month,
  (
    select count(Tags.tagname)
    from Posts
      INNER JOIN PostTags ON PostTags.PostId = Posts.id
      INNER JOIN Tags     ON Tags.id         = PostTags.TagId 
    where
      datepart(year,  Posts.CreationDate) = #Dates.Year and
      datepart(month, Posts.CreationDate) = #Dates.Month and
      tags.tagname = @tag
  ) as count
from #Dates

fork query

created feb 21 11 yow