Average Up Votes Per Post

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

WITH 
PostUpVotes AS (
    SELECT Posts.Id AS PostId,
           COUNT(*) AS VoteCount
    FROM Posts
    JOIN Votes ON (Posts.Id = Votes.PostId) AND Posts.PostTypeId = 1
    WHERE Votes.VoteTypeId = 2 
    AND DATEDIFF(day, Posts.CreationDate, Votes.CreationDate) < 31
    GROUP BY Posts.Id
),
PostDownVotes AS (
    SELECT Posts.Id AS PostId,
           COUNT(*) AS VoteCount
    FROM Posts
    JOIN Votes ON (Posts.Id = Votes.PostId) AND Posts.PostTypeId = 1
    WHERE Votes.VoteTypeId = 3
    AND DATEDIFF(day, Posts.CreationDate, Votes.CreationDate) < 31
    GROUP BY Posts.Id
)

SELECT DATEADD (month, DATEDIFF (month, 0, Posts.CreationDate), 0) AS [Month]
       --,SUM(ISNULL(PostUpVotes.VoteCount, 0)) AS UpVotes
       --,SUM(ISNULL(PostDownVotes.VoteCount, 0)) AS DownVotes
       --,SUM(ISNULL(PostUpVotes.VoteCount, 0)) - SUM(ISNULL(PostDownVotes.VoteCount, 0)) AS Score
       ,CAST(SUM(ISNULL(PostUpVotes.VoteCount, 0)) AS FLOAT) / CAST(COUNT(Posts.Id) AS FLOAT) AS [Upvotes Per Post]
       ,CAST(SUM(ISNULL(PostDownVotes.VoteCount, 0)) AS FLOAT) / CAST(COUNT(Posts.Id) AS FLOAT) AS [Downvotes Per Post]
FROM Posts
LEFT JOIN PostUpVotes ON PostUpVotes.PostId = Posts.Id
LEFT JOIN PostDownVotes ON PostdownVotes.PostId = Posts.Id
WHERE ISNULL(PostUpVotes.VoteCount, 0) - ISNULL(PostDownVotes.VoteCount, 0) > 0
AND Posts.CreationDate > '2009-01-01'
GROUP BY DATEADD (month, DATEDIFF (month, 0, Posts.CreationDate), 0)
ORDER BY DATEADD (month, DATEDIFF (month, 0, Posts.CreationDate), 0)

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results