Site growth

1

Please login or register to vote for this query.

(click on this box to dismiss)

In multiple delete-undelete sequences it can see only the last deletion (not significant error).

Stack Overflow на русском Meta

Вопросы и ответы о сайте для программистов

WITH Cr AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
      1) AS Month,
    COUNT(*) AS Count
  FROM PostsWithDeleted
  WHERE PostTypeId = 1
  GROUP BY
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
      1)
),
Del AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, DeletionDate),
      DATEPART(mm, DeletionDate),
      1) AS Month,
    COUNT(*) AS Count
  FROM PostsWithDeleted
  WHERE
    PostTypeId = 1
    AND
    DeletionDate IS NOT NULL
  GROUP BY
    DATEFROMPARTS(
      DATEPART(yyyy, DeletionDate),
      DATEPART(mm, DeletionDate),
      1)
)
SELECT
  Cr.Month,
  /*
  100.0 *
    SUM(Del.Count) OVER
      (ORDER BY Cr.Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    /
    SUM(Cr.Count - Del.Count) OVER
      (ORDER BY Cr.Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
      AS Pct
  */
  SUM(Cr.Count - Del.Count) OVER
    (ORDER BY Cr.Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS Count,
  SUM(Del.Count) OVER
    (ORDER BY Cr.Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS DelCount
FROM Cr, Del
WHERE
  Cr.Month = Del.Month
ORDER BY Cr.Month

Enter Parameters

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