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