1

Please login or register to vote for this query.

(click on this box to dismiss)

Development of votes per posts over the years, with a separate analysis of votes on current and on old posts

Q&A for people interested in the history and origins of science and mathematics

SELECT 
  PostsOverYears.Year 
    AS Year,
  FORMAT(PostsInYear,'0,,.00 M') 
    AS 'Posts (Year)',
  FORMAT(CumulativePosts,'0,,.00 M') 
    AS 'Posts (All)',
  FORMAT(VotesOnCurrentPosts,'0,,.00 M') 
    AS 'Votes (Current)',
  FORMAT(VotesOnOldPosts,'0,,.00 M') 
    AS 'Votes (Old)',
  FORMAT(VotesOnOldPosts + VotesOnCurrentPosts,'0,,.00 M') 
    AS 'Votes (Year)',
  ROUND((CAST(VotesOnCurrentPosts AS float) / CAST(PostsInYear AS float)), 2)  
    AS 'Votes / Post (Current)',
  CASE 
    WHEN (CumulativePosts - PostsInYear) = 0 THEN 0 
    ELSE ROUND((CAST(VotesOnOldPosts AS float) / CAST((CumulativePosts - PostsInYear) AS float)), 2) 
  END AS 'Votes / Post (Old)'
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

fork query

edited oct 1

created oct 1 Polygnome