Average accept rate percentage, less 0%

0

Please login or register to vote for this query.

(click on this box to dismiss)

Calculate the average percent of each user's questions that have been accepted, excluding those with 0% accept rate.

Stack Overflow

Q&A for professional and enthusiast programmers

-- Average accept rate percentage, less 0%
-- Calculate the average percent of each user's questions that have been accepted, excluding those with 0% accept rate.

SELECT 
    AVG(Round(t.Accepted * 100.0 / t.Total, 1)) AS [Percent],
    STDEVP(Round(t.Accepted * 100.0 / t.Total, 1)) AS [std dev]
  FROM
    (SELECT
         Count(a.Id) AS [Total],
         Sum(CASE q.AcceptedAnswerId WHEN a.Id THEN 1 ELSE 0 END) AS [Accepted]
       FROM Posts AS a
         JOIN Posts AS q
           ON a.ParentId = q.Id
       WHERE q.postTypeId = 1 AND a.postTypeId = 2 AND a.OwnerUserId <> q.OwnerUserId
       GROUP BY a.OwnerUserId
   ) AS t 
  WHERE t.[Accepted] > 0

Enter Parameters

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