1

Please login or register to vote for this query.

(click on this box to dismiss)

Questions without answers count as infinite

Q&A about the site for members of the workforce navigating the professional setting

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

fork query

edited feb 15

created feb 15 peterh - PLEASE DELETE THIS ACCOUNT