Estimated comment deletion probability by month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

Q&A for Ubuntu users and developers

WITH Raw AS (
  SELECT
    Id,
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
      1) AS Month,
    CreationDate
  FROM Comments
),
Raw2 AS (
  SELECT
    Id,
    Month,
    RANK() OVER (PARTITION BY Month ORDER BY CreationDate, Id) AS Rank
  FROM Raw
),
Raw3 AS (
  SELECT
    Month,
    CAST(FLOOR(0.1 * MAX(Rank) + 0.5) AS INTEGER) AS LowLim,
    CAST(FLOOR(0.9 * MAX(Rank) + 0.5) AS INTEGER) AS HighLim,
    MAX(Rank) AS Count
  FROM Raw2
  GROUP BY Month
)
SELECT
  Raw3.Month,
  120.0 * MIN(Raw3.Count) / (MIN(High.Id) - MIN(Low.Id)) AS Psurvival
FROM Raw3, Raw2 Low, Raw2 High
WHERE
  Raw3.Month = Low.Month
  AND
  Raw3.LowLim = Low.Rank
  AND
  Raw3.Month = High.Month
  AND
  Raw3.HighLim = High.Rank
  AND
  Raw3.Count >= 100
GROUP BY Raw3.Month
ORDER BY Month

Enter Parameters

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