Question creation and rejection events by month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Rejection means closure or deletion. For closed and deleted questions, the first one.

Drupal Answers Meta

Q&A about the site for Drupal developers and administrators

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

WITH Rejections AS (
  SELECT
    CASE
      WHEN ClosedDate IS NULL THEN DeletionDate
      WHEN DeletionDate IS NULL THEN ClosedDate
      WHEN ClosedDate < DeletionDate THEN ClosedDate
      ELSE DeletionDate
    END AS RejectionDate
  FROM PostsWithDeleted
  WHERE
    PostTypeId = 1
    AND
    (
      ClosedDate IS NOT NULL
      OR
      DeletionDate IS NOT NULL
    )
),
Raw AS (
  SELECT
    DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) AS Month,
    'Created' AS Type,
    COUNT(*) AS Count
  FROM PostsWithDeleted
  WHERE PostTypeId = 1
  GROUP BY DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1)

  UNION

  SELECT
    DATEFROMPARTS(YEAR(RejectionDate), MONTH(RejectionDate), 1) AS Month,
    'Rejected' AS Type,
    COUNT(*) AS Count
  FROM Rejections
  GROUP BY DATEFROMPARTS(YEAR(RejectionDate), MONTH(RejectionDate), 1)
)
SELECT *
FROM Raw
WHERE Month < @now
ORDER BY
  Month DESC,
  CASE WHEN Type = 'Created' THEN 1 ELSE 2 END

Enter Parameters

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