Skip to main content
replaced http://stackexchange.com/ with https://stackexchange.com/
Source Link

You got the per-site query from Cai, let me add the network-wide query.

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , displayname nvarchar(40)
                       , qcount int
                       , accountid int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , u.displayname
     , count(*)
     , u.accountid
from ' + quotename(name) + '.dbo.posts p
inner join ' + quotename(name) + '.dbo.users u on u.id = p.owneruserid
where posttypeid = 1 -- Q
group by u.displayname
    , u.accountid
'
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

-- show results
select
      'http'https://stackexchange.com/users/' 
      + cast(accountid as nvarchar) 
      +'?tab=top|' + max(displayname) as [network user]
      -- rest of columns
      , sum(qcount) as [# questions network wide]
from #results
group by accountid
order by sum(qcount) desc 

drop table #results

When run today the result looks like this:

most questions asked network wide

And that makes TimTim the winner.

You got the per-site query from Cai, let me add the network-wide query.

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , displayname nvarchar(40)
                       , qcount int
                       , accountid int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , u.displayname
     , count(*)
     , u.accountid
from ' + quotename(name) + '.dbo.posts p
inner join ' + quotename(name) + '.dbo.users u on u.id = p.owneruserid
where posttypeid = 1 -- Q
group by u.displayname
    , u.accountid
'
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

-- show results
select
      'http://stackexchange.com/users/' 
      + cast(accountid as nvarchar) 
      +'?tab=top|' + max(displayname) as [network user]
      -- rest of columns
      , sum(qcount) as [# questions network wide]
from #results
group by accountid
order by sum(qcount) desc 

drop table #results

When run today the result looks like this:

most questions asked network wide

And that makes Tim the winner.

You got the per-site query from Cai, let me add the network-wide query.

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , displayname nvarchar(40)
                       , qcount int
                       , accountid int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , u.displayname
     , count(*)
     , u.accountid
from ' + quotename(name) + '.dbo.posts p
inner join ' + quotename(name) + '.dbo.users u on u.id = p.owneruserid
where posttypeid = 1 -- Q
group by u.displayname
    , u.accountid
'
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

-- show results
select
      'https://stackexchange.com/users/' 
      + cast(accountid as nvarchar) 
      +'?tab=top|' + max(displayname) as [network user]
      -- rest of columns
      , sum(qcount) as [# questions network wide]
from #results
group by accountid
order by sum(qcount) desc 

drop table #results

When run today the result looks like this:

most questions asked network wide

And that makes Tim the winner.

replaced http://data.stackexchange.com/ with https://data.stackexchange.com/
Source Link

You got the per-site query from Cai, let me add the network-wide querynetwork-wide query.

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , displayname nvarchar(40)
                       , qcount int
                       , accountid int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , u.displayname
     , count(*)
     , u.accountid
from ' + quotename(name) + '.dbo.posts p
inner join ' + quotename(name) + '.dbo.users u on u.id = p.owneruserid
where posttypeid = 1 -- Q
group by u.displayname
    , u.accountid
'
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

-- show results
select
      'http://stackexchange.com/users/' 
      + cast(accountid as nvarchar) 
      +'?tab=top|' + max(displayname) as [network user]
      -- rest of columns
      , sum(qcount) as [# questions network wide]
from #results
group by accountid
order by sum(qcount) desc 

drop table #results

When run today the result looks like this:

most questions asked network wide

And that makes Tim the winner.

You got the per-site query from Cai, let me add the network-wide query.

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , displayname nvarchar(40)
                       , qcount int
                       , accountid int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , u.displayname
     , count(*)
     , u.accountid
from ' + quotename(name) + '.dbo.posts p
inner join ' + quotename(name) + '.dbo.users u on u.id = p.owneruserid
where posttypeid = 1 -- Q
group by u.displayname
    , u.accountid
'
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

-- show results
select
      'http://stackexchange.com/users/' 
      + cast(accountid as nvarchar) 
      +'?tab=top|' + max(displayname) as [network user]
      -- rest of columns
      , sum(qcount) as [# questions network wide]
from #results
group by accountid
order by sum(qcount) desc 

drop table #results

When run today the result looks like this:

most questions asked network wide

And that makes Tim the winner.

You got the per-site query from Cai, let me add the network-wide query.

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , displayname nvarchar(40)
                       , qcount int
                       , accountid int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , u.displayname
     , count(*)
     , u.accountid
from ' + quotename(name) + '.dbo.posts p
inner join ' + quotename(name) + '.dbo.users u on u.id = p.owneruserid
where posttypeid = 1 -- Q
group by u.displayname
    , u.accountid
'
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

-- show results
select
      'http://stackexchange.com/users/' 
      + cast(accountid as nvarchar) 
      +'?tab=top|' + max(displayname) as [network user]
      -- rest of columns
      , sum(qcount) as [# questions network wide]
from #results
group by accountid
order by sum(qcount) desc 

drop table #results

When run today the result looks like this:

most questions asked network wide

And that makes Tim the winner.

replaced http://meta.stackexchange.com/ with https://meta.stackexchange.com/
Source Link

You got the per-site query from CaiCai, let me add the network-wide query.

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , displayname nvarchar(40)
                       , qcount int
                       , accountid int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , u.displayname
     , count(*)
     , u.accountid
from ' + quotename(name) + '.dbo.posts p
inner join ' + quotename(name) + '.dbo.users u on u.id = p.owneruserid
where posttypeid = 1 -- Q
group by u.displayname
    , u.accountid
'
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

-- show results
select
      'http://stackexchange.com/users/' 
      + cast(accountid as nvarchar) 
      +'?tab=top|' + max(displayname) as [network user]
      -- rest of columns
      , sum(qcount) as [# questions network wide]
from #results
group by accountid
order by sum(qcount) desc 

drop table #results

When run today the result looks like this:

most questions asked network wide

And that makes Tim the winner.

You got the per-site query from Cai, let me add the network-wide query.

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , displayname nvarchar(40)
                       , qcount int
                       , accountid int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , u.displayname
     , count(*)
     , u.accountid
from ' + quotename(name) + '.dbo.posts p
inner join ' + quotename(name) + '.dbo.users u on u.id = p.owneruserid
where posttypeid = 1 -- Q
group by u.displayname
    , u.accountid
'
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

-- show results
select
      'http://stackexchange.com/users/' 
      + cast(accountid as nvarchar) 
      +'?tab=top|' + max(displayname) as [network user]
      -- rest of columns
      , sum(qcount) as [# questions network wide]
from #results
group by accountid
order by sum(qcount) desc 

drop table #results

When run today the result looks like this:

most questions asked network wide

And that makes Tim the winner.

You got the per-site query from Cai, let me add the network-wide query.

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , displayname nvarchar(40)
                       , qcount int
                       , accountid int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , u.displayname
     , count(*)
     , u.accountid
from ' + quotename(name) + '.dbo.posts p
inner join ' + quotename(name) + '.dbo.users u on u.id = p.owneruserid
where posttypeid = 1 -- Q
group by u.displayname
    , u.accountid
'
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

-- show results
select
      'http://stackexchange.com/users/' 
      + cast(accountid as nvarchar) 
      +'?tab=top|' + max(displayname) as [network user]
      -- rest of columns
      , sum(qcount) as [# questions network wide]
from #results
group by accountid
order by sum(qcount) desc 

drop table #results

When run today the result looks like this:

most questions asked network wide

And that makes Tim the winner.

Source Link
rene Mod
  • 92.1k
  • 17
  • 245
  • 516
Loading