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

2

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").

IT Security

Q&A for information security professionals

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 meta site
loading Hold tight while we fetch your results