0

Please login or register to vote for this query.

(click on this box to dismiss)

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
 )

SELECT uv.QuestionId
     , uv.Title
     , uv.AcceptedAnswerId
     , uv.TotalUpVotes
     , dv.TotalDownVotes
  FROM UpVotes uv
       INNER JOIN DownVotes dv ON dv.AcceptedAnswerId = uv.AcceptedAnswerId
                              AND dv.TotalDownVotes > uv.TotalUpVotes
ORDER BY uv.TotalUpVotes DESC, dv.TotalDownVotes DESC

fork query

edited jan 9

created jan 9 anonymous