1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

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

fork query

edited dec 10

created dec 10 peterh - PLEASE DELETE THIS ACCOUNT