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

Motor Vehicle Maintenance and Repair Meta

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

/* 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 votetypeid = 8)
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,
@includemainmeta = 1,
@CollectResultsForMe = 0

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

Enter Parameters

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