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)

Q&A for amateur radio enthusiasts

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

fork query

edited aug 29 16

created aug 18 16 Mariano