Upvotes and downvotes on HNQ questions

1

Please login or register to vote for this query.

(click on this box to dismiss)

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

SELECT
  HNQed.PostId AS [Post Link],
  HNQed.CreationDate AS HNQedDate,
  COUNT(CASE WHEN questionVotes.VoteTypeId = 2 THEN 1 ELSE NULL END) AS UpVotes,
  COUNT(CASE WHEN questionVotes.VoteTypeId = 3 THEN 1 ELSE NULL END) AS DownVotes,
  COUNT(CASE WHEN questionVotes.VoteTypeId = 2 THEN 1 ELSE NULL END) - COUNT(CASE WHEN questionVotes.VoteTypeId = 3 THEN 1 ELSE NULL END) AS Score,
  COUNT(*) AS TotalVotes,
  ROUND( CAST (COUNT(CASE WHEN questionVotes.VoteTypeId = 3 THEN 1 ELSE NULL END) AS float) / CAST (COUNT(*) AS float) , 2) AS FractionDownvotes,
  Posts.ClosedDate AS ClosedDate
FROM PostHistory AS HNQed
JOIN Votes AS questionVotes ON HNQed.PostId = questionVotes.PostId
JOIN Posts ON HNQed.PostId = Posts.Id
WHERE HNQed.PostHistoryTypeId = 52 -- Question became hot network question
  AND questionVotes.VoteTypeId IN (2,3) -- upvote, downvote
GROUP BY HNQed.PostId, HNQed.CreationDate, Posts.ClosedDate
ORDER BY HNQedDate DESC

Enter Parameters

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