Up vs Down votes by day of week of Question or Answer

0

Please login or register to vote for this query.

(click on this box to dismiss)

Christianity

Q&A for committed Christians, experts in Christianity and those interested in learning more

-- Up vs Down votes by day 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 of the Week],
    Count(*) AS [Post Count],
    SUM(CASE WHEN VoteTypeId = 2 THEN 1 ELSE 0 END) AS [Up Votes],
    SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END) AS [Down Votes],
    ROUND((CAST((SUM(CASE WHEN VoteTypeId = 2 THEN 1 ELSE 0 END) + SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END)) AS float)) / COUNT(p.Id), 4) AS [Votes per Post],
    (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 [Up vs Down Ratio]

FROM
    Votes v JOIN Posts p ON v.PostId=p.Id
GROUP BY
    PostTypeId, DATEPART(WEEKDAY, p.CreationDate), DATENAME(WEEKDAY, p.CreationDate)
ORDER BY
    PostTypeId, DATEPART(WEEKDAY, p.CreationDate)

Enter Parameters

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