Question activity with Downvote events

1

Please login or register to vote for this query.

(click on this box to dismiss)

"Downvote events" means the last time that a question score went to -1 due to a downvote. SO is too big for this query.

Vegetarianism

Q&A for those committed to a vegan or vegetarian lifestyle and anyone interested in learning more

DECLARE @now DATETIME = (
  SELECT TOP 1
    DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1)
  FROM Posts
  ORDER BY Id DESC
);

WITH DownvotedRaw AS (
  SELECT
    PostsWithDeleted.Id AS PostId,
    Votes.CreationDate AS VoteDate,
    Votes.VoteTypeId AS VoteTypeId,
    SUM(5 - 2 * Votes.VoteTypeId)
      OVER (PARTITION BY Votes.PostId ORDER BY Votes.Id) AS Score
  FROM Votes, PostsWithDeleted
  WHERE
    Votes.PostId = PostsWithDeleted.Id
    AND
    PostsWithDeleted.PostTypeId = 1
    AND
    Votes.VoteTypeId BETWEEN 2 AND 3
    AND
    PostsWithDeleted.Score < 0
),
DownvotedRaw2 AS (
  SELECT PostId, MAX(VoteDate) AS DownvotedDate
  FROM DownVotedRaw
  WHERE Score = -1 AND VoteTypeId = 3
  GROUP BY PostId
),
Raw AS (
  SELECT
    DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) AS Month,
    'Created' AS Type,
    COUNT(*) AS Count
  FROM PostsWithDeleted
  WHERE PostTypeId = 1
  GROUP BY DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1)

  UNION
  
  SELECT
    DATEFROMPARTS(YEAR(DownvotedDate), MONTH(DownvotedDate), 1) AS Month,
    'Downvoted' AS Type,
    COUNT(*) AS Count
  FROM DownvotedRaw2
  GROUP BY DATEFROMPARTS(YEAR(DownvotedDate), MONTH(DownvotedDate), 1)

  UNION

  SELECT
    DATEFROMPARTS(YEAR(ClosedDate), MONTH(ClosedDate), 1) AS Month,
    'Closed' AS Type,
    COUNT(*) AS Count
  FROM PostsWithDeleted
  WHERE PostTypeId = 1 AND ClosedDate IS NOT NULL
  GROUP BY DATEFROMPARTS(YEAR(ClosedDate), MONTH(ClosedDate), 1)

  UNION

  SELECT
    DATEFROMPARTS(YEAR(DeletionDate), MONTH(DeletionDate), 1) AS Month,
    'Deleted' AS Type,
    COUNT(*) AS Count
  FROM PostsWithDeleted
  WHERE PostTypeId = 1 AND DeletionDate IS NOT NULL
  GROUP BY DATEFROMPARTS(YEAR(DeletionDate), MONTH(DeletionDate), 1)
)
SELECT *
FROM Raw
WHERE Month < @now
ORDER BY
  Month DESC,
  CASE
    WHEN Type = 'Created' THEN 1
    WHEN Type = 'Downvoted' THEN 2
    WHEN Type = 'Closed' THEN 3
    ELSE 4
  END

Enter Parameters

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