Development of votes per posts over the years, with a separate analysis of votes on current and on old posts
Q&A about the site for professional robotic engineers, hobbyists, researchers and students
SELECT PostsOverYears.Year AS Year, FORMAT(PostsInYear,'0,,.00 M') AS 'Posts [in Year]', FORMAT(CumulativePosts,'0,,.00 M') AS 'Posts (Cumulative)', FORMAT(VotesOnCurrentPosts,'0,,.00 M') AS 'Votes [Current]', FORMAT(VotesOnOldPosts,'0,,.00 M') AS 'Votes (On Old Posts)', ROUND((CAST(VotesOnCurrentPosts AS float) / CAST(PostsInYear AS float)), 2) AS 'Votes / Post [Current Year]', CASE WHEN (CumulativePosts - PostsInYear) = 0 THEN 0 ELSE ROUND((CAST(VotesOnOldPosts AS float) / CAST((CumulativePosts - PostsInYear) AS float)), 2) END AS 'Votes / Post (On Old Posts)' FROM ( SELECT Year, PostsInYear, SUM (PostsInYear) OVER(ORDER BY Year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativePosts FROM ( SELECT YEAR(Posts.CreationDate) AS Year, COUNT(*) PostsInYear FROM Posts GROUP BY YEAR(CreationDate) ) PostsPerYear ) PostsOverYears JOIN ( SELECT Year, SUM(OnCurrent) as VotesOnCurrentPosts, SUM(OnOld) as VotesOnOldPosts FROM ( SELECT YEAR(Votes.CreationDate) AS Year, OnCurrent = CASE WHEN YEAR(Votes.CreationDate) = YEAR(Posts.CreationDate) THEN 1 ELSE 0 END, OnOld = CASE WHEN YEAR(Votes.CreationDate) > YEAR(Posts.CreationDate) THEN 1 ELSE 0 END FROM Votes JOIN Posts ON (Votes.PostId = Posts.Id) ) VotesPerYear GROUP BY (Year) ) VotesOverYears ON(PostsOverYears.Year = VotesOverYears.Year) ORDER BY PostsOverYears.Year ASC