SELECT DATEPART(year, p.CreationDate) AS Year, DATEPART(...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Writing

Q&A for the craft of professional writing, including fiction, non-fiction, technical, scholarly, and commercial writing

-- Detailed Voting and View Count Statistics on Stack Overflow (2020-01-01 to 2024-01-01)

SELECT
    DATEPART(year, p.CreationDate) AS Year,
    DATEPART(month, p.CreationDate) AS Month,
    DATEPART(day, p.CreationDate) AS Day,
    SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END) AS Upvotes,
    SUM(CASE WHEN v.VoteTypeId = 2 AND p.PostTypeId = 1 THEN 1 ELSE 0 END) AS UpvotesQuestions,
    SUM(CASE WHEN v.VoteTypeId = 2 AND p.PostTypeId = 2 THEN 1 ELSE 0 END) AS UpvotesAnswers,
    SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END) AS Downvotes,
    SUM(CASE WHEN v.VoteTypeId = 3 AND p.PostTypeId = 1 THEN 1 ELSE 0 END) AS DownvotesQuestions,
    SUM(CASE WHEN v.VoteTypeId = 3 AND p.PostTypeId = 2 THEN 1 ELSE 0 END) AS DownvotesAnswers,
    SUM(p.ViewCount/1000) AS KViews,
    SUM(CASE WHEN p.PostTypeId = 1 THEN p.ViewCount/1000 ELSE 0 END) AS KViewsQuestions,
    SUM(CASE WHEN p.PostTypeId = 2 THEN p.ViewCount/1000 ELSE 0 END) AS KViewsAnswers
FROM
    Posts p
LEFT JOIN
    Votes v ON p.Id = v.PostId
WHERE
    p.CreationDate BETWEEN '2020-01-01' AND '2024-01-01'
GROUP BY
    DATEPART(year, p.CreationDate),
    DATEPART(month, p.CreationDate),
    DATEPART(day, p.CreationDate)
ORDER BY
    Year, Month, Day;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results