Percentile disc 10%-90% of the age of the deleted question in days, at the time of their deletion

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physical Fitness Meta

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

WITH Raw AS (
  SELECT
    Id,
    DATEFROMPARTS(
      DATEPART(yyyy, DeletionDate),
      DATEPART(mm, DeletionDate),
      1) AS Month,
    1.0 * DATEDIFF(s, CreationDate, DeletionDate) / 86400 AS Days
  FROM PostsWithDeleted
  WHERE
    PostTypeId = 1
    AND
    DeletionDate IS NOT NULL
),
Seq AS (
  SELECT 0.1 AS Id
  UNION SELECT 0.2
  UNION SELECT 0.3
  UNION SELECT 0.4
  UNION SELECT 0.5
  UNION SELECT 0.6
  UNION SELECT 0.7
  UNION SELECT 0.8
  UNION SELECT 0.9
),
Raw2 AS (
  SELECT *, DENSE_RANK() OVER (PARTITION BY Month ORDER BY Days) - 1 AS Rank
  FROM Raw
  WHERE NOT Days BETWEEN 364 AND 373
),
Raw3 AS (
  SELECT MAX(Rank) AS MaxRank, Month
  FROM Raw2
  GROUP BY Month
),
Raw4 AS (
  SELECT FLOOR(Seq.Id * Raw3.MaxRank) AS PctRank, Seq.Id AS SeqId, Raw3.Month
  FROM Raw3, Seq
)
SELECT
  Raw4.Month,
  CAST(Raw4.SeqId AS VARCHAR(MAX)) AS PctDisk,
  Raw2.Days
FROM Raw4, Raw2
WHERE
  Raw4.Month = Raw2.Month
  AND
  Raw4.PctRank = Raw2.Rank
ORDER BY Raw4.Month DESC, Raw4.SeqId DESC

Enter Parameters

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