0

Please login or register to vote for this query.

(click on this box to dismiss)

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
select
    tags.tagname, sum(posts.AnswerCount) AS answercount
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) = 2018 and
    datepart(month, Posts.CreationDate) = 01
Group by tags.tagname


select
    tags.tagname, sum(posts.AnswerCount) AS answercount
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) = 2018 and
    datepart(month, Posts.CreationDate) = 02
Group by tags.tagname



select
    tags.tagname, sum(posts.AnswerCount) AS answercount
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) = 2018 and
    datepart(month, Posts.CreationDate) = 03
Group by tags.tagname


select
    tags.tagname, sum(posts.AnswerCount) AS answercount
INTO #TagCountTemp4
from Posts
    INNER JOIN PostTags ON PostTags.PostId = Posts.id
    INNER JOIN Tags     ON Tags.id         = PostTags.TagId 
where
    datepart(year,  Posts.CreationDate) = 2018 and
    datepart(month, Posts.CreationDate) = 04
Group by tags.tagname


select
    tags.tagname, sum(posts.AnswerCount) AS answercount
INTO #TagCountTemp5
from Posts
    INNER JOIN PostTags ON PostTags.PostId = Posts.id
    INNER JOIN Tags     ON Tags.id         = PostTags.TagId 
where
    datepart(year,  Posts.CreationDate) = 2018 and
    datepart(month, Posts.CreationDate) = 05
Group by tags.tagname


select
    tags.tagname, sum(posts.AnswerCount) AS answercount
INTO #TagCountTemp6
from Posts
    INNER JOIN PostTags ON PostTags.PostId = Posts.id
    INNER JOIN Tags     ON Tags.id         = PostTags.TagId 
where
    datepart(year,  Posts.CreationDate) = 2017 and
    datepart(month, Posts.CreationDate) = 06
Group by tags.tagname


select
    tags.tagname, sum(posts.AnswerCount) AS answercount
INTO #TagCountTemp7
from Posts
    INNER JOIN PostTags ON PostTags.PostId = Posts.id
    INNER JOIN Tags     ON Tags.id         = PostTags.TagId 
where
    datepart(year,  Posts.CreationDate) = 2017 and
    datepart(month, Posts.CreationDate) = 07
Group by tags.tagname


select
    tags.tagname, sum(posts.AnswerCount) AS answercount
INTO #TagCountTemp8
from Posts
    INNER JOIN PostTags ON PostTags.PostId = Posts.id
    INNER JOIN Tags     ON Tags.id         = PostTags.TagId 
where
    datepart(year,  Posts.CreationDate) = 2017 and
    datepart(month, Posts.CreationDate) = 08
Group by tags.tagname


select
    tags.tagname, sum(posts.AnswerCount) AS answercount
INTO #TagCountTemp9
from Posts
    INNER JOIN PostTags ON PostTags.PostId = Posts.id
    INNER JOIN Tags     ON Tags.id         = PostTags.TagId 
where
    datepart(year,  Posts.CreationDate) = 2017 and
    datepart(month, Posts.CreationDate) = 09
Group by tags.tagname


select
    tags.tagname, sum(posts.AnswerCount) AS answercount
INTO #TagCountTemp10
from Posts
    INNER JOIN PostTags ON PostTags.PostId = Posts.id
    INNER JOIN Tags     ON Tags.id         = PostTags.TagId 
where
    datepart(year,  Posts.CreationDate) = 2017 and
    datepart(month, Posts.CreationDate) = 10
Group by tags.tagname


select
    tags.tagname, sum(posts.AnswerCount) AS answercount
INTO #TagCountTemp11
from Posts
    INNER JOIN PostTags ON PostTags.PostId = Posts.id
    INNER JOIN Tags     ON Tags.id         = PostTags.TagId 
where
    datepart(year,  Posts.CreationDate) = 2017 and
    datepart(month, Posts.CreationDate) = 11
Group by tags.tagname


select
    tags.tagname, sum(posts.AnswerCount) AS answercount
INTO #TagCountTemp12
from Posts
    INNER JOIN PostTags ON PostTags.PostId = Posts.id
    INNER JOIN Tags     ON Tags.id         = PostTags.TagId 
where
    datepart(year,  Posts.CreationDate) = 2017 and
    datepart(month, Posts.CreationDate) = 12
Group by tags.tagname


select
    tags.tagname, count(*) AS tagcount
INTO #TagCountTemp13
from Posts
    INNER JOIN PostTags ON PostTags.PostId = Posts.id
    INNER JOIN Tags     ON Tags.id         = PostTags.TagId 
where
    datepart(year,  Posts.CreationDate) = 2018 OR
    datepart(year, Posts.CreationDate) = 2017
Group by tags.tagname



SELECT
    t13.tagname,
    t1.answercount as 'Jan18',
    t2.answercount as 'Feb18',
    t3.answercount as 'Mar18',
    t4.answercount as 'Apr18',
    t5.answercount as 'May18',
    t6.answercount as 'Jun17',
    t7.answercount as 'Jul17',
    t8.answercount as 'Aug17',
    t9.answercount as 'Sep17',
    t10.answercount as 'Oct17',
    t11.answercount as 'Nov17',
    t12.answercount as 'Dec17'

FROM #TagCountTemp13 as t13
LEFT JOIN #TagCountTemp1 as t1
  ON t13.tagname = t1.tagname
LEFT JOIN #TagCountTemp2 as t2
  ON t13.tagname = t2.tagname
LEFT JOIN #TagCountTemp3 as t3
  ON t13.tagname = t3.tagname
LEFT JOIN #TagCountTemp4 as t4
  On t13.tagname = t4.tagname
LEFT JOIN #TagCountTemp5 as t5
  On t13.tagname = t5.tagname
LEFT JOIN #TagCountTemp6 as t6
  On t13.tagname = t6.tagname
LEFT JOIN #TagCountTemp7 as t7
  On t13.tagname = t7.tagname
LEFT JOIN #TagCountTemp8 as t8
  On t13.tagname = t8.tagname
LEFT JOIN #TagCountTemp9 as t9
  On t13.tagname = t9.tagname
LEFT JOIN #TagCountTemp10 as t10
  On t13.tagname = t10.tagname
LEFT JOIN #TagCountTemp11 as t11
  On t13.tagname = t11.tagname
LEFT JOIN #TagCountTemp12 as t12
  On t13.tagname = t12.tagname

fork query

edited jun 21 18

created jun 21 18 Chandler.Nunez