Median and average decision times of suggested edits in hours, by month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Does not count the suggestededits decided by Community (clicking "improve edit").

Stack Overflow на русском Meta

Вопросы и ответы о сайте для программистов

WITH Raw AS (
  SELECT
    SuggestedEdits.Id,
    DATEFROMPARTS(
      YEAR(SuggestedEdits.CreationDate),
      MONTH(SuggestedEdits.CreationDate),
      1) AS Month,
    DATEDIFF(
      s,
      SuggestedEdits.CreationDate,
      COALESCE(ApprovalDate, RejectionDate)
      ) AS TimeS
  FROM SuggestedEdits, Posts
  WHERE
    Posts.Id = SuggestedEdits.PostId
    AND
    (ApprovalDate IS NOT NULL OR RejectionDate IS NOT NULL)
    AND
    (
      (
        PostTypeId BETWEEN 1 AND 2
        AND
        ##ShowPostSuggestions:int?1## <> 0
      )
      OR
      (
        PostTypeId BETWEEN 4 AND 5
        AND
        ##ShowWikiSuggestions:int?1## <> 0
      )
    )
    AND
    SuggestedEdits.Id NOT IN (
      SELECT SuggestedEditId FROM SuggestedEditVotes WHERE UserId = -1
    )
)
SELECT DISTINCT
  Month,
  1.0 *
    PERCENTILE_CONT(0.5)
      WITHIN GROUP (ORDER BY TimeS) OVER (PARTITION BY Month)
    / 3600
    AS MedianHours,
  1.0 * AVG(1.0 * TimeS) OVER (PARTITION BY Month) /3600 AS AverageHours
FROM Raw
ORDER BY Month DESC

Enter Parameters

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