Question Votes by Quarter

0

Please login or register to vote for this query.

(click on this box to dismiss)

Number of votes on questions by quarter, along with the percentage that were up-votes and down-votes.

Stack Overflow

Q&A for programmers

with quarters as (
    SELECT
        DATEPART(yyyy, Votes.CreationDate) as Year,
        DATEPART(q, Votes.CreationDate) as Quarter,
        COUNT(*) as Total,
        SUM(CASE when VoteTypes.Name = 'UpMod' then 1 else 0 END) as Up,
        SUM(CASE when VoteTypes.Name = 'DownMod' then 1 else 0 END) as Down
    FROM Votes
    LEFT JOIN VoteTypes on Votes.VoteTypeId=VoteTypes.Id
    LEFT JOIN Posts on Votes.PostId=Posts.Id
    WHERE PostTypeId=1
    GROUP BY DATEPART(yyyy, Votes.CreationDate),DATEPART(q, Votes.CreationDate)
)

select
    Year,
    Quarter,
    Up + Down as Votes,
    CAST(convert(numeric(5,2), cast(Up as float) * 100 / (Up+Down)) as VARCHAR(10)) + '%' as Up,
    CAST(convert(numeric(5,2), cast(Down as float) * 100 / (Up+Down)) as VARCHAR(10)) + '%' as Down
from quarters
order by Year, Quarter

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached