0

Please login or register to vote for this query.

(click on this box to dismiss)

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

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

view current version

edited apr 17

created apr 12 rene