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

Вопросы и ответы для программистов

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


SELECT TOP 50
    t1.tagname,
    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))/2 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 >= 15 and
    t2.tagcount-t1.tagcount >= 15 and
    t3.tagcount-t2.tagcount >= 15

ORDER BY '%b' desc

fork query

edited may 27 18 anonymous

created feb 15 11 yow