instruction: Selects all posts where the number of upvotes is greater than the number of downvotes, but where the number of downvotes is at least half the number of upvotes. The result is ordered by the number of upvotes (the highest on top).. Selects all posts where the number of upvotes is greater than the number of downvotes, but where the number of downvotes is at least half the number of upvotes. The result is ordered by the number of upvotes (the highest on top).
response: SELECT CONCAT('https://stackoverflow.com/a/', PostId), Upvotes, Downvotes FROM (SELECT PostId, Upvotes, Downvotes, 2 * Downvotes / NULLIF(Upvotes, 0) AS Ratio FROM (SELECT PostId, Upvotes = SUM(CASE WHEN VoteTypeId = 2 THEN 1 ELSE 0 END), Downvotes = SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END) FROM Votes WHERE VoteTypeId IN (2, 3) GROUP BY PostId) AS VoteCounts INNER JOIN Posts ON Posts.Id = VoteCounts.PostId WHERE Upvotes > 0 AND Downvotes > 0) AS Scores WHERE Ratio >= 1 ORDER BY Upvotes DESC