Find answers that are ACCEPTED but have MORE DOWNVOTES THAN upvotes.

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- 1. Get All the Questions with an accepted answer
;WITH Questions AS
(
  SELECT TOP 100000
         qp.Id as QuestionId
       , qp.Title
       , qp.AcceptedAnswerId
    FROM dbo.Posts qp
         INNER JOIN dbo.PostTypes qpt ON qpt.Id = qp.PostTypeId
                                     AND qpt.Name = 'Question'
   WHERE qp.AcceptedAnswerId IS NOT NULL
)                                   
-- 2. Find all UpVotes per Accepted Answer
, UpVotes AS
(
SELECT q.QuestionId
     , q.Title
     , q.AcceptedAnswerId
     , COUNT(v.Id) AS TotalUpVotes
  FROM dbo.Votes v
       INNER JOIN dbo.VoteTypes vt ON vt.Id = v.VoteTypeId
                                  AND vt.Name = 'UpMod'
       INNER JOIN Questions q on q.AcceptedAnswerId = v.PostId
 GROUP BY  q.QuestionId, q.Title, q.AcceptedAnswerId
 )

-- 3. Find all DownVotes per Accepted Answer
, DownVotes AS
(
SELECT q.QuestionId
     , q.AcceptedAnswerId
     , COUNT(v.Id) AS TotalDownVotes
  FROM dbo.Votes v
       INNER JOIN dbo.VoteTypes vt ON vt.Id = v.VoteTypeId
                                  AND vt.Name = 'DownMod'
       INNER JOIN Questions q on q.AcceptedAnswerId = v.PostId
 GROUP BY  q.QuestionId, q.AcceptedAnswerId
 )
-- 4. Find the User of each accepted Answer
, AcceptedAnswerUser AS
( 
  SELECT 
         q.QuestionId
       , q.Title
       , q.AcceptedAnswerId
       , ap.Id as UserId
       , u.DisplayName as UserName
    FROM dbo.Posts ap
         INNER JOIN Questions q ON q.AcceptedAnswerId = ap.Id
         INNER JOIN dbo.Users u on u.Id = ap.OwnerUserId
)
-- 5. Select the data where downvotes are greater than upvotes
SELECT uv.QuestionId
     , uv.Title
     , uv.AcceptedAnswerId
     , aau.UserName
     , uv.TotalUpVotes AS AcceptedAnswerUpvoteCount
     , dv.TotalDownVotes AS AcceptedAnswerDownvoteCount
  FROM UpVotes uv
       INNER JOIN DownVotes dv ON dv.AcceptedAnswerId = uv.AcceptedAnswerId
                              AND dv.TotalDownVotes > uv.TotalUpVotes
       INNER JOIN AcceptedAnswerUser aau on aau.AcceptedAnswerId = uv.AcceptedAnswerId
ORDER BY uv.TotalUpVotes DESC, dv.TotalDownVotes DESC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results