1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those interested in the geology, meteorology, oceanography, and environmental sciences

DECLARE @Raw TABLE (
  RegYear INT NOT NULL,
  VoteMonth DATETIME NOT NULL,
  Count INT NOT NULL,
  PRIMARY KEY(RegYear, VoteMonth)
);

INSERT INTO @Raw
SELECT
  YEAR(Users.CreationDate) AS RegYear,
  DATEFROMPARTS(YEAR(Votes.CreationDate), MONTH(Votes.CreationDate), 1)
    AS VoteMonth,
  COUNT(*) AS Count
FROM Votes, Posts, Users
WHERE
  Votes.VoteTypeId = 2
  AND
  Votes.PostId = Posts.Id
  AND
  Posts.OwnerUserId = Users.Id
GROUP BY
  YEAR(Users.CreationDate),
  DATEFROMPARTS(YEAR(Votes.CreationDate), MONTH(Votes.CreationDate), 1);

WITH VoteMonths AS (
  SELECT VoteMonth, SUM(Count) AS Count
  FROM @Raw Raw
  GROUP BY VoteMonth
),
RegYears AS (
  SELECT RegYear
  FROM @Raw Raw
  GROUP BY RegYear
)
SELECT
  VoteMonths.VoteMonth,
  CAST(RegYears.RegYear AS VARCHAR(MAX)) AS RegYear,
  100.0 * SUM(COALESCE(Raw.Count, 0)) OVER (
    PARTITION BY VoteMonths.VoteMonth
    ORDER BY RegYears.RegYear
    ROWS UNBOUNDED PRECEDING
  ) / VoteMonths.Count AS Pct
FROM VoteMonths
LEFT JOIN RegYears ON 1=1
LEFT JOIN @Raw Raw ON
  VoteMonths.VoteMonth = Raw.VoteMonth
  AND
  RegYears.RegYear = Raw.RegYear
ORDER BY VoteMonths.VoteMonth, RegYears.RegYear DESC

fork query

edited jun 10 20

created jun 10 20 peterh - PLEASE DELETE THIS ACCOUNT