0

Please login or register to vote for this query.

(click on this box to dismiss)

assisted by JNK http://stackoverflow.com/questions/5001393/query-for-fastest-growing-tags-in-data-explorer/5004684#5004684

Q&A for professional and enthusiast programmers

-- QUERY: tags with highest increase in growth, over 3 months
-- assisted by JNK http://stackoverflow.com/questions/5001393/query-for-fastest-growing-tags-in-data-explorer/5004684#5004684

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

select
    tags.tagname, count(*) AS tagcount
INTO #TagCountTemp1
from Posts
    INNER JOIN PostTags ON PostTags.PostId = Posts.id
    INNER JOIN Tags     ON Tags.id         = PostTags.TagId 
where
    datepart(year,  Posts.CreationDate) = 2010 and
    datepart(month, Posts.CreationDate) = 11 and
    tags.tagname = @tag
group by tags.tagname

select
    tags.tagname, count(*) AS tagcount
INTO #TagCountTemp2
from Posts
    INNER JOIN PostTags ON PostTags.PostId = Posts.id
    INNER JOIN Tags     ON Tags.id         = PostTags.TagId 
where
    datepart(year,  Posts.CreationDate) = 2010 and
    datepart(month, Posts.CreationDate) = 12 and
    tags.tagname = @tag
group by tags.tagname


select
    tags.tagname, count(*) AS tagcount
INTO #TagCountTemp3
from Posts
    INNER JOIN PostTags ON PostTags.PostId = Posts.id
    INNER JOIN Tags     ON Tags.id         = PostTags.TagId
where
    datepart(year,  Posts.CreationDate) = 2011 and
    datepart(month, Posts.CreationDate) = 1 and
    tags.tagname = @tag
group by tags.tagname



SELECT TOP 20
    @tag as 'tag',
    t1.tagcount as 'Mon1',
    t2.tagcount as 'Mon2',
    t3.tagcount as 'Mon3',
    t2.tagcount-t1.tagcount as IncA,
    t3.tagcount-t2.tagcount as IncB,
    100*(t2.tagcount-t1.tagcount)/t1.tagcount as '%a',
    100*(t3.tagcount-t2.tagcount)/t2.tagcount as '%b',

    (100*(t3.tagcount-t2.tagcount)/t2.tagcount
    - 100*(t2.tagcount-t1.tagcount)/t1.tagcount) as 'diff'

FROM #TagCountTemp1 as t1
LEFT JOIN #TagCountTemp2 as t2
  ON t1.tagname = t2.tagname
LEFT JOIN #TagCountTemp3 as t3
  ON t1.tagname = t3.tagname


ORDER BY diff desc

fork query

created feb 20 11 yow