Tag popularity

0

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the number of a tag per month, and how many percent of the total questions that use the tag.

Bicycles

Q&A for people who build and repair bicycles, people who train cycling, or commute on bicycles

-- Enter Query Title
-- Enter Query Description
DECLARE @tag nvarchar(25) = ##tag:string##;

with total as (
select datepart(year, posts.creationdate) as year,
datepart(month, posts.creationdate) as month, 
count(distinct posts.id) as questions
from posts
inner join posttags on posttags.postid = posts.id
inner join tags on tags.id = posttags.tagid
where posts.posttypeid = 1
group by datepart(month, posts.creationdate), 
datepart(year, posts.creationdate)
), tagtable as (
select datepart(year, posts.creationdate) as year,
datepart(month, posts.creationdate) as month, 
count(distinct posts.id) as questions
from posts
inner join posttags on posttags.postid = posts.id
inner join tags on tags.id = posttags.tagid
where posts.posttypeid = 1 and tags.tagname = @tag
group by datepart(month, posts.creationdate), 
datepart(year, posts.creationdate)
)
select total.year, total.month, tagtable.questions as tagged_questions, cast(tagtable.questions as float) / total.questions as ratio
from total
    join tagtable on total.year = tagtable.year and total.month = tagtable.month
order by total.year, total.month

Enter Parameters

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