Monthly activity distribution by year of registration

1

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

WITH Raw AS (
  SELECT
    DATEPART(yyyy, Users.CreationDate) AS UserYear,
    DATEFROMPARTS(
      DATEPART(yyyy, Posts.CreationDate),
      DATEPART(m, Posts.CreationDate),
      1
    ) AS PostMonth,
    COUNT(*) AS Count
  FROM Posts, Users
  WHERE
    Posts.OwnerUserId = Users.Id
    AND
    Posts.OwnerUserId > 0
  GROUP BY
    DATEPART(yyyy, Users.CreationDate),
      DATEFROMPARTS(
      DATEPART(yyyy, Posts.CreationDate),
      DATEPART(m, Posts.CreationDate),
      1
    )
),
RawSum AS (
  SELECT
    PostMonth,
    SUM(Count) AS Count
  FROM Raw
  GROUP BY PostMonth
),
UserYears AS (
  SELECT DISTINCT UserYear FROM Raw
),
Raw2 AS (
  SELECT
    RawSum.PostMonth,
    UserYears.UserYear,
    COALESCE(Raw.Count, 0) AS Count
  FROM RawSum
  INNER JOIN UserYears ON 1=1
  LEFT JOIN Raw
    ON RawSum.PostMonth = Raw.PostMonth AND UserYears.UserYear = Raw.UserYear
)
SELECT
  Raw2.PostMonth,
  CAST(Raw2.UserYear AS VARCHAR(MAX)) AS UserYear,
  100.0 * SUM(Raw2.Count) OVER (
    PARTITION BY Raw2.PostMonth ORDER BY Raw2.UserYear
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    / RawSum.Count AS Pct
FROM Raw2, RawSum
WHERE Raw2.PostMonth = RawSum.PostMonth
ORDER BY Raw2.PostMonth, Raw2.UserYear

Enter Parameters

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