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