1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

CREATE TABLE #TagVotes (
  TagId INT,
  Month DATETIME,
  Votes INT
);

WITH Raw AS (
  SELECT TOP ##TopN:int?7##
    PostTags.TagId,
    COUNT(*) AS Count
  FROM Votes, Posts, PostTags
  WHERE
    Votes.PostId = Posts.Id
    AND
    COALESCE(Posts.ParentId, Posts.Id) = PostTags.PostId
    AND
    Votes.CreationDate >= ##TopNSince:string?2010##
  GROUP BY PostTags.TagId
  ORDER BY Count DESC, PostTags.TagId
)
INSERT INTO #TagVotes
SELECT
  PostTags.TagId,
  DATEFROMPARTS(
    DATEPART(yyyy, Votes.CreationDate),
    DATEPART(mm, Votes.CreationDate),
    1) AS Month,
  COUNT(*) AS UserVoteCount
FROM Posts, Votes, PostTags
WHERE
  Posts.Id = Votes.PostId
  AND
  Votes.VoteTypeId = ##VoteTypeId:int?2##
  AND
  Votes.CreationDate >= ##ShowSince:string?2010##
  AND
  COALESCE(Posts.ParentId, Posts.Id) = PostTags.PostId
  AND
  PostTags.TagId IN (
    SELECT TagId
    FROM Raw
  )
GROUP BY
  DATEFROMPARTS(
    DATEPART(yyyy, Votes.CreationDate),
    DATEPART(mm, Votes.CreationDate),
    1),
  PostTags.TagId;

WITH Months AS (
  SELECT Month, SUM(Votes) AS TotalVoteCount
  FROM #TagVotes
  GROUP BY Month
),
TopTags AS (
  SELECT
    #TagVotes.TagId,
    MIN(Tags.TagName) AS TagName
  FROM #TagVotes, Tags
  WHERE #TagVotes.TagId = Tags.Id
  GROUP BY #TagVotes.tagId
)
SELECT
  Months.Month,
  TopTags.TagName,
  100.0 * SUM(COALESCE(#TagVotes.Votes, 0)) OVER (
    PARTITION BY Months.Month
    ORDER BY TopTags.TagId ROWS UNBOUNDED PRECEDING
  ) / Months.TotalVoteCount AS Pct
FROM TopTags
LEFT JOIN Months ON 1=1
LEFT JOIN #TagVotes ON
  #TagVotes.Month = Months.Month
  AND
  #TagVotes.TagId = TopTags.TagId
ORDER BY Month DESC, TopTags.TagId DESC;

fork query

edited may 28

created may 28 peterh - PLEASE DELETE THIS ACCOUNT