Inactive Users who First asked ITG

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.

Arqade

Q&A for passionate videogamers on all platforms

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

Enter Parameters

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