Percentage of well-received questions by month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Well-received means: 1) not closed 2) not deleted 3) has a positive score after 30 days. Invalidated votes and long close-reopen-delete-undelete histories cause a little error, but not a significant one.

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

WITH Raw AS (
  SELECT
    PostsWithDeleted.Id,
    DATEFROMPARTS(
      DATEPART(yyyy, MIN(PostsWithDeleted.CreationDate)),
      DATEPART(mm, MIN(PostsWithDeleted.CreationDate)),
      1) AS Month,
    SUM(CASE WHEN Votes.VoteTypeId = 2 THEN 1 ELSE -1 END) AS Score,
    MIN(PostsWithDeleted.ClosedDate) AS IsClosed,
    MIN(PostsWithDeleted.DeletionDate) AS IsDeleted
  FROM PostsWithDeleted, Votes
  WHERE
    PostsWithDeleted.PostTypeId = 1
    AND
    Votes.VoteTypeId BETWEEN 2 AND 3
    AND
    Votes.PostId = PostsWithDeleted.Id
    AND
    DATEDIFF(day, PostsWithDeleted.CreationDate, Votes.CreationDate)
      BETWEEN 0 AND 30
  GROUP BY PostsWithDeleted.Id
)
SELECT
  Month,
  100.0 *
    COUNT(CASE
      WHEN Score > 0 AND IsClosed IS NULL AND IsDeleted IS NULL THEN 1
      ELSE NULL END)
      / COUNT(*) AS WellReceivedPct
  /*
  COUNT(*) AS AllQ,
  COUNT(CASE WHEN Score > 0 AND IsClosed IS NULL AND IsDeleted IS NULL THEN 1
    ELSE NULL END) AS WellReceivedQ
  */
FROM Raw
GROUP BY Month
ORDER BY Month DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results