Q&A about the site for sound designers
; WITH CLOSEDQUESTIONS(Id, Score, AScore, VotesNeededToDelete) AS ( SELECT-- top 1000 p.Id, p.Score, SUM(a.Score) AS AScore, CASE WHEN 3 + ((p.Score + Sum(a.Score)) /20)>10 THEN 10 ELSE 3 + ((p.Score + Sum(a.Score)) /20) END as VotesNeededToDelete FROM Posts as p INNER JOIN Posts as a ON a.ParentID = p.Id WHERE p.PostTypeId=1 AND p.ClosedDate IS NOT NULL AND EXISTS (SELECT * FROM Votes AS v WHERE v.PostId = p.Id) GROUP BY p.Id, p.Score ) SELECT TOP 30 Q.Id AS [Post Link], --MAX(Q.Score), MAX(Q.AScore), MAX(Q.VotesNeededToDelete) - COUNT(v.VoteTypeId) AS VotesToGo FROM CLOSEDQUESTIONS AS Q INNER JOIN Votes AS v ON v.PostId = Q.Id WHERE v.VoteTypeId=10 GROUP BY Q.Id HAVING MAX(Q.VotesNeededToDelete) - COUNT(v.VoteTypeId)=1 --ORDER BY VotesToGo