Technology Trends (% of Questions per Tag per Month)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

select PerTag.Month, PerTag.TagName, PerTag.NumQuestions * 1.0 / Overall.NumQuestions * 100 as [% of Questions]
from (
  select
    dateadd(mm, (Year(Posts.CreationDate) - 1900) * 12 + Month(Posts.CreationDate) - 1, 0) AS Month,
    Tags.TagName,
    count(*) as NumQuestions

  from string_split(##CommaSeparatedTagList:string##, ',') as TagList
  inner join Tags on Tags.TagName = LTrim(RTrim(TagList.value))
  left join PostTags on PostTags.TagId = Tags.Id
  left join Posts on Posts.Id = PostTags.PostId
  left join PostTypes on PostTypes.Id = Posts.PostTypeId

  where
    PostTypes.Name = 'Question' and
    
    Posts.CreationDate >= ##StartDate:string?2009-01-01## and

    -- Exclude the current month
    Posts.CreationDate < dateadd(mm, datediff(mm, 0, getdate()), 0)

  group by Year(Posts.CreationDate), Month(Posts.CreationDate), Tags.TagName
) as PerTag
left join (
  select
    dateadd(mm, (Year(Posts.CreationDate) - 1900) * 12 + Month(Posts.CreationDate) - 1, 0) AS Month,
    count(*) as NumQuestions
  from Posts
  left join PostTypes on PostTypes.Id = Posts.PostTypeId
  where PostTypes.Name = 'Question'
  group by Year(Posts.CreationDate), Month(Posts.CreationDate)
) as Overall on PerTag.Month = Overall.Month

order by
  PerTag.Month, TagName

Enter Parameters

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