Relative popularity of the most liked tags

1

Please login or register to vote for this query.

(click on this box to dismiss)

Poker Meta

Q&A about the site for serious players and enthusiasts of poker

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##
  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;

SELECT * FROM #TagVotes;

/*
WITH Months AS (
  SELECT Month, SUM(Votes) AS TotalVoteCount
  FROM #UserVotes
  GROUP BY Month
),
TopUsers AS (
  SELECT
    #UserVotes.UserId,
    MIN(Users.DisplayName) + ' ('
      + CAST(#UserVotes.UserId AS VARCHAR(MAX)) + ')'
      AS UserName
  FROM #UserVotes, Users
  WHERE #UserVotes.UserId = Users.Id
  GROUP BY #UserVotes.UserId
)
SELECT
  Months.Month,
  TopUsers.UserName,
  100.0 * SUM(COALESCE(#UserVotes.Votes, 0)) OVER (
    PARTITION BY Months.Month
    ORDER BY TopUsers.UserId ROWS UNBOUNDED PRECEDING
  ) / Months.TotalVoteCount AS Pct
FROM TopUsers
LEFT JOIN Months ON 1=1
LEFT JOIN #UserVotes ON
  #UserVotes.Month = Months.Month
  AND
  #UserVotes.UserId = TopUsers.UserId
ORDER BY Month DESC, TopUsers.UserId DESC;

Enter Parameters

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