5

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for committed Christians, experts in Christianity and those interested in learning more

/*
  Inspiration for cross-site query from:
    Mark Hurd, http://data.stackexchange.com/stackoverflow/query/69700/generate-emailhash-comparison
    Tim Stone, http://data.stackexchange.com/stackoverflow/query/72411
 */
/*
  We build a dynamic query as a prelude, then @Template for each site
  with @Separator in between, and finally a postlude.
  First, the template, which may contain the following escape strings:
    ~n  the "pretty" site name (or close enough), e.g. 'Dba'
    ~u  the URL of the site, e.g. http://dba.stackexchange.com
    ~~  a literal '~'
 */

declare @monthsago as DATETIME = DateAdd(mm, 0 - ##MonthsAgo:int?0##, CURRENT_TIMESTAMP);

create table #badges (
BadgeName NVarchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL);

--insert into #badges values ('Rank');
insert into #badges values ('Caucus');
insert into #badges values ('Legendary');
insert into #badges values ('Epic');
insert into #badges values ('Fanatic');
insert into #badges values ('Marshal');
insert into #badges values ('Generalist');
insert into #badges values ('Sportsmanship');
insert into #badges values ('Strunk & White');
insert into #badges values ('Copy Editor');
insert into #badges values ('Steward');
insert into #badges values ('Electorate');
insert into #badges values ('Curious');
insert into #badges values ('Inquisitive');
insert into #badges values ('Socratic');
insert into #badges values ('Explainer');
insert into #badges values ('Refiner');
insert into #badges values ('Illuminator');

declare @sitedbname as NVarchar(100),
        @sitedispname as NVarchar(100),
        @pivot as nvarchar(MAX),
        @query as nvarchar(MAX);

SET @pivot = STUFF((SELECT ',' + QUOTENAME(BadgeName) 
            FROM #badges
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

create table #sitestats (
    Site NVarchar(250),
    BadgeName NVarchar(50),
    Awards int);
    
declare SITES cursor for

select Substring(Name, 0, Len(Name) - 4 )
from sys.Databases
where Name like '%.Meta'
  --and Name = 'StackExchange.Codereview.Meta'
  and not Name = 'StackExchange.Meta'
UNION select 'StackExchange.Meta'

open SITES

fetch next from SITES into @sitedbname
while @@FETCH_STATUS = 0
BEGIN

    declare @org as NVarchar(100), @http as NVarchar(100), @pos as int;
    
    select @org = lower(@sitedbname),
           @http = 'http://',
           @pos = CharIndex('.', @sitedbname),
           @sitedispname = case when @sitedbname like 'StackExchange.%'
                           then substring(@sitedbname, 15, 100)
                           else @sitedbname
                           end;
    if @pos > 0 begin
        select @http = @http + substring(@org, @pos + 1, len(@org) - @pos) + '.',
               @org = left(@org, @pos - 1)
    end
    
    select @http = @http + @org + '.com/help/badges'

    print 'Processing site ' + @sitedbname + ' -> ' + @http
    set @query = '
           insert into #sitestats
           select ''' + @http + '|' + @sitedispname + ''',
                  BadgeName,
                  Count(UserId)
           FROM #badges left outer join [' + @sitedbname + ']..Badges
                   on BadgeName = Name
                   and Date <= ''' + Convert(NVarchar(30), @MonthsAgo) + '''
           Group by BadgeName'
           
          ; 
           
    print @query

    EXEC sp_executesql @query

    fetch next from SITES into @sitedbname
END

close SITES
deallocate SITES
;

with Rankings as (
    select Site as Site,
           BadgeName as BadgeName,
           (select count (*)
            from #sitestats yy
            where yy.BadgeName = xx.BadgeName
              and yy.Awards > xx.awards) as Rank
    from #sitestats xx
), AvgRanks as (
    select Site as Site,
           Avg(1.0 * Rank) as Overall
    from Rankings
    where BadgeName <> 'Caucus'
    group by Site
)
insert into #sitestats
select Site,
       'Rank',
       1 + (select count(*)
            from AvgRanks sub
            where sup.Overall > sub.Overall)
from AvgRanks sup
       
set @query = '
select *
from (select * from #sitestats) as ss                          
PIVOT (SUM(ss.Awards) FOR ss.BadgeName IN ( [Rank], ' + @pivot + ' )
) AS BadgePivot';

execute(@query)

view current version

edited oct 12 14

created jun 5 14 rolfl