Percentage Answered (2 weeks ago)

0

Please login or register to vote for this query.

(click on this box to dismiss)

SITE HEALTH VALUE from Area51 statistics -> Percentage Answered Total Questions vs. Accepted posts + Unaccepted with answer voted 1+ (=Answered)

Stack Overflow

Q&A for professional and enthusiast programmers

WITH Answers AS (
  /* unique answers with best score per question */
  SELECT ParentId AS Id, MAX(Score) AS Score
  FROM Posts
  WHERE PostTypeId = 2
  GROUP BY ParentId
)
, Counts AS (
  SELECT
    /* Count for questions joined with answers */
    COUNT(*) AS Asked,
    SUM(IIF(A.Id IS NULL, 0, 1)) AS Answered,
    SUM(IIF(Q.AcceptedAnswerId IS NULL, 0, 1)) AS Accepted, 
    SUM(IIF(Q.AcceptedAnswerId IS NULL AND A.Score > 0, 1, 0))
      AS VotedNotAccepted,
    SUM(IIF(Q.ClosedDate IS NULL, 1, 0)) AS AskedNotClosed,
    SUM(IIF(A.Id IS NULL OR Q.ClosedDate IS NOT NULL, 0, 1)) AS AnsweredNotClosed,
    SUM(IIF(Q.AcceptedAnswerId IS NULL OR Q.ClosedDate IS NOT NULL, 0, 1)) AS AcceptedNotClosed, 
    SUM(IIF(Q.AcceptedAnswerId IS NULL AND A.Score > 0 AND Q.ClosedDate IS NULL, 1, 0))
      AS VotedNotAcceptedNotClosed
  FROM Posts Q
  LEFT OUTER JOIN Answers A
    ON Q.Id = A.Id
  WHERE Q.PostTypeId = 1
  AND Q.CreationDate <= DateAdd(week, -2, Convert( datetime, ##SEDEupdated:string?2016-08-28 08:00:00## ))
)
  
SELECT 
  Asked, Answered,
  Cast(Cast(
    Answered / CAST(Asked AS FLOAT)
    *100 as decimal(18,2)) as varchar(5)) + ' %' AS "%Answered",
  Accepted, VotedNotAccepted, 
  (Accepted + VotedNotAccepted) AS AcceptedOrVoted, 
  Cast(Cast(
    (Accepted + VotedNotAccepted) / CAST(Asked AS FLOAT)
    *100 as decimal(18,2)) as varchar(5)) + ' %' AS "%AcceptedOrVoted",
  AskedNotClosed,
  AnsweredNotClosed,
  Cast(Cast(
    AnsweredNotClosed / CAST(AskedNotClosed AS FLOAT)
    *100 as decimal(18,2)) as varchar(5)) + ' %' AS '%AnsweredNotClosed',
  AcceptedNotClosed, VotedNotAcceptedNotClosed,
  (AcceptedNotClosed + VotedNotAcceptedNotClosed) AS AcceptedOrVotedNotClosed, 
  Cast(Cast(
    (AcceptedNotClosed + VotedNotAcceptedNotClosed) / CAST(AskedNotClosed AS FLOAT)
    *100 as decimal(18,2)) as varchar(5)) + ' %' AS "%AcceptedOrVotedNotClosed"
FROM Counts

Enter Parameters

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