1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for active researchers, academics and students of physics

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

fork query

edited nov 12 19

created aug 29 19 E.P.