Total inactive good reputation users who just visit

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

Arts and Crafts Meta

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

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results