Median delay of first answers by month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Questions without answers count as infinite

Bicycles Meta

Q&A about the site for people who build and repair bicycles, people who train cycling, or commute on bicycles

DECLARE @now DATETIME = (
  SELECT TOP 1 DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1)
  FROM PostsWithDeleted
  ORDER BY Id DESC
);

WITH FirstARaw AS (
  SELECT Q.Id AS QId, MIN(A.CreationDate) AS FirstATS
  FROM PostsWithDeleted Q, PostsWithDeleted A
  WHERE A.ParentId = Q.Id
  GROUP BY Q.Id
),
FirstA AS (
  SELECT
    Q.Id AS QId,
    Q.CreationDate AS QTS,
    CASE WHEN FirstARaw.QId IS NOT NULL THEN FirstATS ELSE '2099-12-31' END
      AS FirstATS
  FROM PostsWithDeleted Q
  LEFT JOIN FirstARaw ON Q.Id = FirstARaw.QId
  WHERE Q.PostTypeId = 1
),
Raw AS (
  SELECT
    DATEFROMPARTS(YEAR(QTS), MONTH(QTS), 1) AS Month,
    DATEDIFF(MI, QTS, FirstATS) AS MinDiff
  FROM FirstA
)
SELECT DISTINCT
  Month,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY MinDiff) OVER (PARTITION BY Month)
    AS MedianDelayMin
FROM Raw
WHERE Month < @now
ORDER BY Month

Enter Parameters

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