/* 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)

Artificial Intelligence

Q&A for people interested in conceptual questions about life and challenges in a world where "cognitive" functions can be mimicked in purely digital environment

/* 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##

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 = '
begin try
if db_name() <> ''StackOverflow'' then
begin
  delete from #users 
  where accountid in 
  ( select 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'')
  )
end
end try
begin catch 
   print error_message()
end catch 
',
@includemainsites = 1,
@includemainmeta = 1,
@CollectResultsForMe = 0

select *
from #users

Enter Parameters

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