Q&A about the site for music historians, critics, and fans
SET NOCOUNT ON DECLARE @VoteStats TABLE (PostId int, up int, down int) INSERT @VoteStats SELECT v.PostId, up = SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END), down = SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END) FROM Votes v WHERE v.PostId BETWEEN 70000000 AND 80000000 AND v.VoteTypeId IN (2, 3) GROUP BY v.PostId SET NOCOUNT OFF SELECT p.id, p.body, p.title, p.creationdate, p.tags, vs.up, vs.down, u.Id as UserId, u.Location, u.DisplayName, u.aboutme FROM @VoteStats vs INNER JOIN Posts p ON vs.PostId = p.Id INNER JOIN Users u ON p.OwnerUserId = u.Id WHERE vs.up < 1 AND vs.down > 1 AND p.CommunityOwnedDate IS NULL AND p.ClosedDate IS NULL AND LEN(u.Location) > 1 AND ( u.Location LIKE '%United States%' OR u.Location LIKE '%India%' OR u.Location LIKE '%United Kingdom%' OR u.Location LIKE '%Germany%' OR u.Location LIKE '%Canada%' OR u.Location LIKE '%France%' OR u.Location LIKE '%Brazil%' OR u.Location LIKE '%Russia%' OR u.Location LIKE '%Australia%' OR u.Location LIKE '%Italy%' ) AND p.body NOT LIKE '%<code>%' ORDER BY vs.up DESC