Initial quality timeseries

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

;WITH NumberedUpDownVotes AS
(
  SELECT
    Posts.Id,
    Posts.PostTypeId,
    Votes.VoteTypeId,
    Votes.CreationDate,
    ROW_NUMBER() OVER (
      PARTITION BY
        Votes.PostId
      ORDER BY
        Votes.CreationDate
    ) AS RowNumber
  FROM Posts
    INNER JOIN Votes ON Votes.PostId = Posts.Id
      AND Votes.VoteTypeId IN (2, 3)
)
SELECT
  YEAR(NumberedUpDownVotes.CreationDate) AS [Year],
  MONTH(NumberedUpDownVotes.CreationDate) AS [Month],
  COUNT(*) AS PostCount,
  SUM(CASE NumberedUpDownVotes.PostTypeId WHEN 1 THEN 1 ELSE 0 END) AS QuestionCount,
  SUM(CASE NumberedUpDownVotes.PostTypeId WHEN 2 THEN 1 ELSE 0 END) AS AnswerCount,
  SUM(CASE NumberedUpDownVotes.VoteTypeId WHEN 2 THEN 1 ELSE 0 END) AS PostUpFirstCount,
  SUM(CASE NumberedUpDownVotes.VoteTypeId WHEN 3 THEN 1 ELSE 0 END) AS PostDownFirstCount,
  SUM(CASE WHEN NumberedUpDownVotes.PostTypeId = 1 AND NumberedUpDownVotes.VoteTypeId = 2 THEN 1 ELSE 0 END) AS QuestionUpFirstCount,
  SUM(CASE WHEN NumberedUpDownVotes.PostTypeId = 1 AND NumberedUpDownVotes.VoteTypeId = 3 THEN 1 ELSE 0 END) AS QuestionDownFirstCount,
  SUM(CASE WHEN NumberedUpDownVotes.PostTypeId = 2 AND NumberedUpDownVotes.VoteTypeId = 2 THEN 1 ELSE 0 END) AS AnswerUpFirstCount,
  SUM(CASE WHEN NumberedUpDownVotes.PostTypeId = 2 AND NumberedUpDownVotes.VoteTypeId = 3 THEN 1 ELSE 0 END) AS AnswerDownFirstCount
FROM NumberedUpDownVotes
WHERE NumberedUpDownVotes.RowNumber = 1
GROUP BY
  YEAR(NumberedUpDownVotes.CreationDate),
  MONTH(NumberedUpDownVotes.CreationDate)
ORDER BY
  YEAR(NumberedUpDownVotes.CreationDate),
  MONTH(NumberedUpDownVotes.CreationDate)

Enter Parameters

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