0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for developers and users of elementary OS and applications

DECLARE @TagId INT;
SELECT @TagId = Id
FROM Tags
WHERE TagName=##TagName:string##;

WITH PostStat AS (
  SELECT
    DATEFROMPARTS(
      YEAR(Posts.CreationDate),
      MONTH(Posts.CreationDate),
      1) AS Month,
    COUNT(*) AS Count
  FROM Posts
  WHERE Posts.PostTypeId = 1
  GROUP BY DATEFROMPARTS(
    YEAR(Posts.CreationDate),
    MONTH(Posts.CreationDate),
    1)
),
TagStat AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, Posts.CreationDate),
      DATEPART(mm, Posts.CreationDate),
      1) AS Month,
    COUNT(*) AS Count
  FROM Posts, PostTags
  WHERE
    Posts.PostTypeId = 1
    AND
    PostTags.PostId = Posts.Id
    AND
    PostTags.TagId = @TagId
  GROUP BY DATEFROMPARTS(
    DATEPART(yyyy, Posts.CreationDate),
    DATEPART(mm, Posts.CreationDate),
    1)
)
SELECT
  PostStat.Month,
  100.0 * TagStat.Count / PostStat.Count AS Pct
FROM PostStat
LEFT JOIN TagStat ON PostStat.Month = TagStat.Month
ORDER BY Month

view current version

edited may 26 20

created apr 17 19 peterh - PLEASE DELETE THIS ACCOUNT