Percent Answered (in SE sites or '% Answered' in Area51 statistics)

5

Please login or register to vote for this query.

(click on this box to dismiss)

SITE HEALTH VALUE from Area51 statistics -> Percentage Answered (http://area51.stackexchange.com/proposals/42810/stack-overflow-in-spanish) --- Percent Answered in SE sites (http://stackexchange.com/sites#percentanswered) ---- Formula: %Answered = (Accepted + Answer_Has_Positive_Score + Closed) / Questions_Including_Closed ---- Source: http://meta.stackexchange.com/a/86976/304899

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

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
)
  
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 sites:
loading Hold tight while we fetch your results