Votes per month

0

Please login or register to vote for this query.

(click on this box to dismiss)

English Language Learners

Q&A for speakers of other languages learning English

WITH UpVotesByMonth  AS (
    SELECT DATEADD (month, DATEDIFF (month, 0, v.CreationDate), 0)  AS [Month],
    COUNT (v.Id) AS Votes

    FROM Votes v 
    JOIN Posts p ON (p.Id = v.PostId) AND p.PostTypeId = 1
    WHERE v.VoteTypeId=2 and DATEDIFF(day, p.CreationDate, v.CreationDate) < 31

    GROUP BY DATEADD (month, DATEDIFF (month, 0, v.CreationDate), 0)
),
DownVotesByMonth  AS (
    SELECT DATEADD (month, DATEDIFF (month, 0, v.CreationDate), 0)  AS [Month],
    COUNT (v.Id) AS Votes

    FROM Votes v 
    JOIN Posts p ON (p.Id = v.PostId) AND p.PostTypeId = 1
    WHERE v.VoteTypeId=3 and DATEDIFF(day, p.CreationDate, v.CreationDate) < 31

    GROUP BY DATEADD (month, DATEDIFF (month, 0, v.CreationDate), 0)
)

SELECT
    v.[Month], 
    v.Votes+dv.Votes AS 'total',
    v.Votes AS 'upvotes',
    dv.Votes AS 'downvotes'

FROM UpVotesByMonth v
JOIN DownVotesByMonth dv ON v.[Month] = dv.[Month]
ORDER BY v.[Month]

Enter Parameters

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