Total # questions and # closed/deleted questions posted by 1-rep accounts

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- MinUserAge: Ignore users created within the last ## days:
-- MinTotalQs: Ignore users with fewer than ## questions posted:
-- MinClosedQs: Ignore users with fewer than ## closed/deleted questions:
-- MaxTotalQs: Ignore users with more than ## questions posted:
DECLARE @MinUserAge int = ##MinUserAge:int?30##
DECLARE @MinTotalQs int = ##MinTotalQs:int?1##
DECLARE @MinClosedQs int = ##MinClosedQs:int?1##
DECLARE @MaxTotalQs int = ##MaxTotalQs:int?1##

SELECT u.Id AS [User Link],
(SELECT COUNT(q.Id) FROM PostsWithDeleted q WHERE q.OwnerUserId = u.Id AND q.PostTypeId = 1) AS 'Total Questions',
(SELECT COUNT(q.Id) FROM PostsWithDeleted q WHERE q.OwnerUserId = u.Id AND q.PostTypeId = 1 AND (q.ClosedDate IS NOT NULL or q.DeletionDate IS NOT NULL)) AS 'Closed/Deleted Questions',
(SELECT COUNT(q.Id) FROM Posts q WHERE q.OwnerUserId = u.Id AND q.PostTypeId = 2) AS 'Total Non-deleted Answers'

FROM Users u

WHERE u.Reputation = 1 AND u.Id > 0 AND DATEDIFF(day, u.CreationDate, CURRENT_TIMESTAMP) > @MinUserAge AND
  (SELECT COUNT(q.Id) FROM PostsWithDeleted q WHERE q.OwnerUserId = u.Id AND q.PostTypeId = 1) >= @MinTotalQs AND
  (SELECT COUNT(q.Id) FROM PostsWithDeleted q WHERE q.OwnerUserId = u.Id AND q.PostTypeId = 1) <= @MaxTotalQs AND
  (SELECT COUNT(q.Id) FROM PostsWithDeleted q WHERE q.OwnerUserId = u.Id AND q.PostTypeId = 1 AND (q.ClosedDate IS NOT NULL or q.DeletionDate IS NOT NULL)) >= @MinClosedQs

GROUP BY u.Id

ORDER BY (SELECT COUNT(q.Id) FROM PostsWithDeleted q WHERE q.OwnerUserId = u.Id AND q.PostTypeId = 1) ASC,
 (SELECT COUNT(q.Id) FROM PostsWithDeleted q WHERE q.OwnerUserId = u.Id AND q.PostTypeId = 1 AND (q.ClosedDate IS NOT NULL or q.DeletionDate IS NOT NULL)) ASC,
 (SELECT COUNT(q.Id) FROM Posts q WHERE q.OwnerUserId = u.Id AND q.PostTypeId = 2) ASC

Enter Parameters

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