Downvotes on self-answered questions

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

WITH Totals AS (
  SELECT q.Id, q.Score, COALESCE(COUNT(v.Id), 0) AS Downvotes
    FROM Posts AS q
    LEFT OUTER JOIN Votes AS v
      ON v.PostId = q.Id
     AND v.VoteTypeId = 3 -- downvotes
    WHERE q.PostTypeId = 1
    GROUP BY q.Id, q.Score
), SelfAnsweredTotals AS (
  SELECT q.Id, q.Score, COALESCE(COUNT(v.Id), 0) / COUNT(DISTINCT(a.Id)) AS Downvotes
    FROM Posts AS q
    INNER JOIN Posts AS a
      ON a.ParentId = q.Id
     AND a.OwnerUserID = q.OwnerUserId
    LEFT OUTER JOIN Votes AS v
      ON v.PostId = q.Id
     AND v.VoteTypeId = 3 -- downvotes    
    GROUP BY q.Id, q.Score
)
SELECT 'All questions',
  COUNT(*) AS 'Total',
  ROUND(AVG(CAST(Score AS FLOAT)), 2) AS 'Average score',
  ROUND(AVG(CAST(Downvotes AS FLOAT)), 2) AS 'Average # of downvotes',
  AVG(CASE Downvotes WHEN 0 THEN 0 ELSE 100 END) AS '% downvoted'
  FROM Totals
UNION ALL
SELECT 'Self-answered',
  COUNT(*) AS 'Total',
  ROUND(AVG(CAST(Score AS FLOAT)), 2) AS 'Average score',
  ROUND(AVG(CAST(Downvotes AS FLOAT)), 2) AS 'Average # of downvotes',
  AVG(CASE Downvotes WHEN 0 THEN 0 ELSE 100 END) AS '% downvoted'
  FROM SelfAnsweredTotals

Enter Parameters

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