Ratio of first-posts by month

1

Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace Meta

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

WITH FP AS (
  SELECT
    PostTypeId,
    COUNT(*) AS Count,
    DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) AS Month
  FROM Posts
  WHERE Id IN (
    SELECT MIN(Id) AS Id
    FROM Posts
    WHERE PostTypeId BETWEEN 1 AND 2 AND OwnerUserId > 0
    GROUP BY OwnerUserId, PostTypeId
  )
  GROUP BY
    PostTypeId,
    DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1)
),
AP AS (
  SELECT
    PostTypeId,
    COUNT(*) AS Count,
    DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) AS Month
  FROM Posts
  WHERE PostTypeId BETWEEN 1 AND 2 AND OwnerUserId > 0
  GROUP BY
    PostTypeId,
    DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1)
)
SELECT AP.Month, PostTypes.Name, 100.0 * FP.Count / AP.Count
FROM AP, FP, PostTypes
WHERE
  AP.Month = FP.Month
  AND
  AP.PostTypeId = FP.PostTypeId
  AND
  AP.PostTypeId = PostTypes.Id
ORDER BY AP.Month DESC

Enter Parameters

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