Compare tag popularity by month

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

WITH TagsRaw AS (
  SELECT
    Tags.Id AS TagId,
    Tags.TagName AS TagName,
    p.Pattern AS Pattern
  FROM
    Tags,
    (
    SELECT value AS Pattern
    FROM STRING_SPLIT(##TagPatternCsv:string?python-2%,python-3%##,',')
    ) p
  WHERE
    Tags.TagName LIKE p.Pattern
),
VoteStat AS (
  SELECT
    DATEFROMPARTS(
      YEAR(Votes.CreationDate),
      MONTH(Votes.CreationDate),
      1) AS VoteMonth,
    COUNT(*) AS Count
  FROM Votes
  GROUP BY
    DATEFROMPARTS(
      YEAR(Votes.CreationDate),
      MONTH(Votes.CreationDate),
      1)
),
Raw AS (
  SELECT
    DATEFROMPARTS(
      YEAR(Votes.CreationDate),
      MONTH(Votes.CreationDate),
      1) AS VoteMonth,
    TagsRaw.Pattern,
    COUNT(*) AS Count
  FROM TagsRaw, PostTags, PostsWithDeleted, Votes
  WHERE
    TagsRaw.TagId = PostTags.TagId
    AND
    PostTags.PostId
      = COALESCE(PostsWithDeleted.ParentId, PostsWithDeleted.Id)
    AND
    Votes.PostId = PostsWithDeleted.Id
  GROUP BY
    DATEFROMPARTS(
      YEAR(Votes.CreationDate),
      MONTH(Votes.CreationDate),
      1), TagsRaw.Pattern
)
SELECT
  Raw.VoteMonth,
  Raw.Pattern,
  100.0 * Raw.Count / VoteStat.Count AS Pct
FROM Raw, VoteStat
WHERE
  Raw.VoteMonth = VoteStat.VoteMonth
ORDER BY VoteMonth, Pattern

Enter Parameters

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