2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about Stack Overflow, Server Fault and Super User

-- Up vs Down votes by hour of week of Question or Answer

SELECT
    CASE WHEN PostTypeId = 1 THEN 'Question' ELSE 'Answer' END As [Post Type],
    DATENAME(WEEKDAY, p.CreationDate) AS [Day],
    DATEPART(HOUR, p.CreationDate) AS [Hour],
    Count(*) AS Amount,
    SUM(CASE WHEN VoteTypeId = 2 THEN 1 ELSE 0 END) AS UpVotes,
    SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END) AS DownVotes,
    (CAST(SUM(CASE WHEN VoteTypeId = 2 THEN 1 ELSE 0 END) AS float) / CAST(SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END) AS float)) AS UpVoteDownVoteRatio
FROM
    Votes v JOIN Posts p ON v.PostId=p.Id
WHERE
    VoteTypeId In (2,3)
 AND
    PostTypeId In (1,2)
GROUP BY
    PostTypeId, DATEPART(WEEKDAY, p.CreationDate), DATENAME(WEEKDAY, p.CreationDate), DATEPART(HOUR, p.CreationDate)
ORDER BY
    PostTypeId, --DATEPART(WEEKDAY, p.CreationDate), DATEPART(HOUR, p.CreationDate)
7

fork query

edited jun 6 12

created may 29 10 Mark Hurd