Velocity of questions, by year, for several popular languages and frameworks

0

Please login or register to vote for this query.

(click on this box to dismiss)

Finds the total posts across several tags for ##Start Year## and ##End Year##, and shows the corresponding rate of change. Can be adapted to show different tags, or to further drill down by month. There is one major problem in that this query doesn't account for multiple tags. If a question is tagged `python` and `django`, it will show in both counts.

Game Development Meta

Q&A about the site for professional and independent game developers

-- Velocity of questions, by year, for several popular languages and frameworks
-- Finds the total posts across several tags for ##Start Year## and ##End Year##, and shows the
-- corresponding rate of change. Can be adapted to show different tags, or to further drill
-- down by month.
-- There is one major problem in that this query doesn't account for multiple tags. If a question
-- is tagged `python` and `django`, it will show in both counts.

-- Useful query: Start Year = 2009, End Year = 2010

WITH a AS
(
SELECT posts = count(p.Id), tag = t.TagName, year = YEAR(p.CreationDate), month = MONTH(p.CreationDate)
FROM Posts p
JOIN PostTags pt
ON p.Id = pt.PostId
JOIN Tags t
ON pt.TagId = t.Id
WHERE 
  (  t.TagName = 'django' 
  OR t.TagName = 'scala'
  OR t.TagName = 'python'
  OR t.TagName = 'ruby-on-rails'
  OR t.TagName = 'ruby'
  OR t.TagName = 'java'
  OR t.TagName = '.net'
  OR t.TagName = 'c#'
  OR t.TagName = 'javascript'
  OR t.TagName = 'jquery')
GROUP BY t.TagName, YEAR(p.CreationDate), MONTH(p.CreationDate)

)

SELECT 
  tag      = startyear.tag,
  [Posts 2009]   = startyear.posts,
  [Posts 2010]   = endyear.posts,
  velocity = round(cast(endyear.posts as float) / cast(startyear.posts as float),3)
FROM a startyear
JOIN a endyear
ON startyear.tag = endyear.tag
WHERE startyear.year = ##startyear## AND endyear.year = ##endyear##
ORDER BY round(cast(endyear.posts as float) / cast(startyear.posts as float),3) DESC

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results