0

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the tags a user posted in for their first <= 2 questions and/or answers, broken down by post type.

Q&A for computer enthusiasts and power users

-- Most Popular Tags for a User's First Posts
-- Shows the tags a user posted in for their first <= 2 questions and/or answers,
-- broken down by post type.

SELECT
  d.PostTypeId,
  d.r AS Rank,
  d.Tag,
  d.PostCount
  FROM
  (
    SELECT
      c.PostTypeId,
      c.Tag,
      c.PostCount,
      ROW_NUMBER() OVER(PARTITION BY c.PostTypeId ORDER BY c.PostCount DESC) AS r
      FROM
      (
        SELECT
          b.PostTypeId,
          t.TagName AS Tag,
          COUNT(1) AS PostCount
          FROM
          (
            SELECT
              a.PostTypeId,
              a.PostId
              FROM
              (
                SELECT
                  p.PostTypeId,
                  COALESCE(p.ParentId, p.Id) AS PostId,
                  ROW_NUMBER() OVER(PARTITION BY p.PostTypeId, u.Id ORDER BY p.CreationDate) AS r
                  FROM Users u
                  INNER JOIN Posts p ON p.OwnerUserId = u.Id
                  WHERE p.PostTypeId IN (1, 2)
              ) a
              WHERE a.r <= 2
          ) b
          INNER JOIN PostTags pt ON pt.PostId = b.PostId
          INNER JOIN Tags t ON t.Id = pt.TagId
          GROUP BY b.PostTypeId, t.TagName
      ) c
  ) d
  WHERE r <= 25

fork query

created jun 16 11 Jon Seigel