0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for active researchers, academics and students of physics

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

SELECT
  u.Id AS [User Link],
  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
  
  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.PostTypeId = 1
  )
  -- 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'

fork query

edited dec 30 14

created dec 30 14 senshin