Votes per post over the years

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

Drupal Answers

Q&A for Drupal developers and administrators

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

Enter Parameters

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