Accepted answer percentage over time

0

Please login or register to vote for this query.

(click on this box to dismiss)

accepted answers, total answers, and their ratio: for each month; tabulated over time

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @UserId int = ##UserId:int?5026##

SELECT
  A.Month as 'Month',
  SUM(A.AC) as 'AC',
  SUM(B.TOTAL) as 'Total', 
  ROUND(CAST(SUM(A.AC) AS float) / SUM(B.TOTAL) * 100, 2)  as 'AC %age'
  
FROM
  (SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, a.CreationDate),
      DATEPART(mm, a.CreationDate),
    1) AS 'Month',
    COUNT(*) as 'AC'
    FROM Posts q
      JOIN Posts a
      ON q.AcceptedAnswerId = a.Id AND q.PostTypeId = 1 AND a.PostTypeId = 2
    WHERE a.OwnerUserId = @UserId
    GROUP BY DATEFROMPARTS(
      DATEPART(yyyy, a.CreationDate),
      DATEPART(mm, a.CreationDate),
    1)) as A

  JOIN
  
   (SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
    1) AS 'Month',
    COUNT(*) as 'Total'
    FROM Posts
    WHERE OwnerUserId = @UserId AND PostTypeId = 2
    GROUP BY DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
    1)) as B

  ON A.Month = B.Month

  GROUP BY A.Month
  ORDER BY A.Month DESC;

Enter Parameters

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