better median


Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

-- all databases
declare  db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 5 -- skip master, temp, model, msdb, Data.SE
                         and database_id <> db_id() -- skip current db as well
declare @db_c_name sysname   -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string

-- result table
create table #all_accounts ( accountid int)

-- create a union of all accountid's across
-- all databases
set @sql = N'insert into #all_accounts '                            
open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
    set @sql = @sql + N'select accountid from ' + QUOTENAME(@db_c_name) + N'.dbo.users'
   fetch next from db_c into @db_c_name
   IF (@@FETCH_STATUS = 0) 
     set @sql = @sql + N' 
close db_c
deallocate db_c

print @sql
exec(@sql) -- execute the insert with the unions

select  reputation median
       , rnasc
       , rndesc from
(select u.reputation, 
rnasc = row_number() over(order by u.reputation),
rndesc = row_number() over(order by u.reputation desc)
 from [users] u
 where u.lastaccessdate > dateadd(m, -6, getdate()) -- active in last 6 months ago
 and u.reputation > 1 -- more then 1 rep 
 and exists (select 1 from posts where owneruserid = -- visible post
 and exists (select 1 from #all_accounts where accountid = u.accountid)  --other accounts in the network
) b
where rnasc between rndesc - 1 and rndesc + 1

select count(*)
from #all_accounts

select count(*)
from users

select count(*)
from users u
left outer join #all_accounts acc on acc.accountid = u.accountid
 where acc.accountid is null

drop table #all_accounts

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results