Vampires1

1

Please login or register to vote for this query.

(click on this box to dismiss)

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

DECLARE @last_dump datetime = (SELECT MAX(CreationDate) FROM PostHistory)

SELECT
  u.Id,
  u.Reputation,
  u.UpVotes + u.DownVotes AS [Votes],
  (SELECT COUNT(Id) FROM Posts WHERE OwnerUserId = u.Id AND PostTypeId = 1) AS [Questions],
  (SELECT COUNT(Id) FROM Posts WHERE OwnerUserId = u.Id AND PostTypeId = 2) AS [Answers],
  (SELECT COUNT(Id) FROM Posts WHERE OwnerUserId = u.Id AND PostTypeId IN (1, 2)) AS [Posts],
  (SELECT COUNT(Id) FROM Comments WHERE UserId = u.Id) AS [Comments],
  (SELECT COUNT(c3.Id) FROM Comments AS c3 INNER JOIN Posts AS p3 ON c3.PostId = p3.Id WHERE c3.UserId = u.Id AND p3.OwnerUserId != u.Id) AS [CommentsOnOthers]
FROM
  Users AS u
WHERE
  -- Find users whose first post on the site was a question tagged with 
  -- identification-request
  EXISTS (
    SELECT 1
    FROM 
      Posts AS p2
      INNER JOIN (SELECT p.OwnerUserId AS [OwnerUserId],
                         MIN(CreationDate) AS [MinCreationDate]
                  FROM Posts AS p 
                  -- Exclude tag wikis because of <http://meta.stackexchange.com/q/246351/224428>
                  WHERE p.PostTypeId IN (1, 2, 3)
                  GROUP BY p.OwnerUserId) AS thing
        ON p2.CreationDate = thing.[MinCreationDate]
    WHERE thing.OwnerUserId = u.Id
          AND p2.Tags LIKE '%identify-this-%'
  )
  -- Exclude users who have less than 4 weeks of activity in the data dump
  AND u.CreationDate < @last_dump - 28
  -- Exclude users who joined during private beta
  AND u.CreationDate > '2012-12-18'

Enter Parameters

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