The REAL median on SO

0

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)
begin
    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) 
   BEGIN   
     set @sql = @sql + N' 
     union 
     '
   END
end
close db_c
deallocate db_c

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

-- http://stackoverflow.com/a/7227860/578411
select avg(reputation) median from
(select u.reputation, 
rnasc = row_number() over(order by u.reputation),
rndesc = row_number() over(order by u.reputation desc)
 from [users] u
 -- http://meta.stackexchange.com/questions/146450/delete-old-unloved-users/146452#146452
 where u.lastaccessdate > dateadd(m, -1, getdate()) -- active in last week
 and u.reputation > 125 -- more then 1 rep 
 and exists (select 1 from posts where owneruserid = u.id) -- 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

Options:
Switch sites:
loading Hold tight while we fetch your results