0

Please login or register to vote for this query.

(click on this box to dismiss)

This query attempts to find users who's First or Second post (Question or Answer) was ITG and then did not go on to become active Users.

Q&A about the site for professional and independent game developers

-- Inactive Users who First asked ITG
-- This query attempts to find users who's First or Second post (Question or Answer) was ITG and then did not go on to become active Users.

SELECT
  d.DisplayName,
  d.UpVotes,
  d.Tag
  FROM
  (
    SELECT
      c.DisplayName,
      c.UpVotes,
      c.Tag,
      c.PostCount
      FROM
      (
        SELECT
          b.PostTypeId,
          b.DisplayName,
          b.UpVotes,
          t.TagName AS Tag,
          COUNT(1) AS PostCount
          FROM
          (
            SELECT
              a.PostTypeId,
              a.PostId,
              a.UpVotes,
              a.DisplayName              
              FROM
              (
                SELECT
                  p.PostTypeId, u.DisplayName, u.UpVotes,
                  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
          WHERE t.TagName='identify-this-game'
          GROUP BY b.PostTypeId, t.TagName, b.DisplayName, b.UpVotes
      ) c
  ) d
  WHERE d.UpVotes < 2

fork query

created jul 7 11 tzenes