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

Craft CMS

Q&A for administrators, end users, developers and designers for Craft CMS

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