QUERY: tags with highest increase in growth, over 3 years

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

Stack Overflow

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, 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) = 2019 
    --and
    --datepart(month, Posts.CreationDate) = 11
Group by tags.tagname
Order by tagcount DESC

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) = 2018 
    --and
    --datepart(month, Posts.CreationDate) = 12
Group by tags.tagname
Order by tagcount DESC


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) = 2017 
    --and
    --datepart(month, Posts.CreationDate) = 1
Group by tags.tagname
Order by tagcount DESC


SELECT TOP 200
    t1.tagname,
    t1.tagcount as '2019',
    t2.tagcount as '2018',
    t3.tagcount as '2017',
    t2.tagcount-t1.tagcount as Inc1819,
    t3.tagcount-t2.tagcount as Inc1817,
    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
where
    t1.tagcount >= 3 and
    t2.tagcount-t1.tagcount >= 3 and
    t3.tagcount-t2.tagcount >= 3

ORDER BY diff desc

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results