0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for music historians, critics, and fans

DECLARE @SelectedTag varchar(35) = ##SelectedTag:string?google-sheets##

( -- Questions
select year(Posts.CreationDate) as [Post Year], 'Questions' as [Type], count(*) as [Total Count]
FROM Tags
JOIN PostTags on Tags.Id = PostTags.TagId 
JOIN Posts on Posts.Id = PostId
JOIN Votes v on Posts.Id = v.PostId
where TagName = @SelectedTag
AND v.PostId is null and Posts.PostTypeId = 1
AND year(Posts.CreationDate) >= 2010
AND year(Posts.CreationDate) < year(GETDATE())
group by year(Posts.CreationDate)
)
UNION
( -- Answers 
select year(Posts.CreationDate) as [Post Year], 'Answers' as [Type], count(*) as [Total Count]
from Tags
JOIN PostTags on Tags.Id = PostTags.TagId 
JOIN Posts on Posts.Id = PostId
JOIN Votes v on Posts.Id = v.PostId
where TagName = @SelectedTag
AND v.PostId is null and Posts.PostTypeId = 2
AND year(Posts.CreationDate) >= 2010
AND year(Posts.CreationDate) < year(GETDATE())
group by year(Posts.CreationDate)

)
order by year(Posts.CreationDate);

fork query

edited feb 3 Rubén

created feb 23 20 avalokan