create table #validaccounts(accountid int primary key) s...

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

create table #validaccounts(accountid int primary key)

select accountid 
into #users
from users
where reputation = 1
and creationdate >= '##year##-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)


declare @sql nvarchar(max)
select @sql = concat(N'insert into #validaccounts 
select distinct accountid 
from (',
STRING_AGG(concat(N'
select #users.accountid 
from [', convert(nvarchar(max),name), N'].dbo.users uo
inner join #users on #users.accountid = uo.accountid
where uo.reputation > 1
or uo.upvotes > 0
or uo.downvotes > 0 
-- or exists (select 1 from badges where userid = uo.id and name <>''Autobiographer'')
' ) ,N' union all ')
,N' ) data')
from sys.databases
where database_id > 5
and not name = db_name()
and (name not like '%.Meta' or name = 'StackExchange.Meta')

exec(@sql)

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