/* this query creates a #users table with all users that...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

/* this query creates a #users table with all users  that
 are created after YEAR
 have barely accessed the site 
  have no posts
 have no badges other than 'Autobiographer'
 have no "votes" (aka bounties because that is the only thing we have)
 have no other account on any other site that
   have rep > 1
   have given up or down votes
   have any badges
*/
declare @yr int = ##year##

create table #validaccounts(accountid int)

select accountid 
into #users
from users
where reputation = 1
and creationdate between concat(@yr, '-01-01') and concat(@yr + 1, '-01-01')
AND datediff(hour, Creationdate, Lastaccessdate) < 504
and not exists(select 1 from posts where owneruserid = users.id)
and not exists(select 1 from badges where userid = users.id and name <> 'Autobiographer')
and not exists(select 1 from votes where userid = users.id)
and users.accountid not in (7585603)

-- create clustered index #ix_users_ok on #users(accountid)

exec sede_ineachdb @sqlcommand = '
  insert into #validaccounts 
  select uo.accountid 
  from users uo
  inner join #users u on u.accountid = uo.accountid
  where uo.reputation > 1
  or uo.upvotes > 0
  or uo.downvotes > 0 
  -- or exists (select * from badges where userid = uo.id and name <>''Autobiographer'')
',
@includemainsites = 1,
@includemetasites = 0,
@includemainmeta = 1,
@CollectResultsForMe = 0

delete from #users
where accountid in (select accountid from #validaccounts)

select *
from #users

Enter Parameters

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