1

Please login or register to vote for this query.

(click on this box to dismiss)

Where "inactive" is defined as "hasn't logged on in the past year".

Q&A about the site for artists and crafters

declare @inactiveDays int
declare @checkingDate datetime
declare @now datetime
declare @minReputation int
declare @joinedDate datetime

set @inactiveDays = 60
set @minReputation = 3000
set @joinedDate = '2015-01-01'

set @now = getdate()
set @checkingDate = dateadd(day, -@inactiveDays, @now)

SELECT 
DisplayName,
Reputation,
u.CreationDate
FROM Users as u
WHERE (u.Reputation > @minReputation AND u.CreationDate <= @joinedDate) 
AND (
  SELECT COUNT(c.id) as CommentsCount
  FROM Comments as c
  WHERE c.CreationDate > @checkingDate 
  AND c.UserId = u.Id
) = 0
AND (
  SELECT COUNT(p.Id) as PostsCount
  FROM Posts as p
  WHERE (p.LastEditDate > @checkingDate OR p.CreationDate > @checkingDate)
  AND p.OwnerUserId = u.Id
) = 0
AND (
  SELECT COUNT(v.Id) as VotesCount
  FROM Votes as v
  WHERE v.CreationDate > @checkingDate
  AND v.UserId = u.Id
) = 0
AND u.LastAccessDate > @checkingDate
ORDER BY u.Reputation DESC

fork query

edited jan 15 Mr_Green

created dec 16 zcoop98