0

Please login or register to vote for this query.

(click on this box to dismiss)

The average number of votes (upvotes and downvotes) on answers per month

Q&A for scientific skepticism

-- Average Number of Votes on Answers per Month
-- The average number of votes (upvotes and downvotes) on answers per month

SELECT     YEAR(v.CreationDate) year, 
           MONTH(v.CreationDate) month, 
           CAST(COUNT(*) AS float) / 
           (
              SELECT CAST (COUNT(*) AS float)
              FROM   posts p
              WHERE  YEAR(p.CreationDate) = YEAR(v.CreationDate) AND
                     MONTH(p.CreationDate) = MONTH(v.CreationDate) AND
                     p.PostTypeId = 2
           ) votes_per_answer
FROM       votes v
INNER JOIN posts p ON (p.id = v.postid AND p.PostTypeId = 2)
GROUP BY   YEAR(v.CreationDate), MONTH(v.CreationDate)
ORDER BY   1 DESC, 2 DESC

view current version

edited sep 30 10

created sep 30 10 Daniel Vassallo