5

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for software engineers working on automated testing, continuous delivery, service integration and monitoring, and building SDLC infrastructure

/* HOW DOES THIS WORK?

First, let's hand-pick some badges that are loosely representative of
user activity on the site. Badges like 'Student' are not as good of an
indictor of value-contribution as badges like 'Sportsmanship',
or 'Strunk & White'. Now, the badge-type selection plays a big role, so
look at the badges and decide whether you agree that they are a meaningful
indication of site 'value'.

Note that 'Caucus' is listed in the results. This is a special badge that is
not used to rank the sites. The `Caucus` badge is awarded when there's an
election, so Caucus is only used to identify graduated, and non-graduated
sites.

So, OK, we have a selection of badges, what do we do with them? Well, we count
how many times each badge has been awarded on each site. We end up with the
bulk of the data you see in the results below. That is a count of each time
each badge was awarded on each site.

Now, we rank the sites. This is a 3 stage process.

The first step is to process all the sites for just one badge. For example,
badge `Curious`. We order the sites by the number of Curious badges awarded.

The site with the most awarded, is badge-ranked 1, and so on. If two sites
share the same count, they have the same badge-rank.

Now, what we do is we 'average' the ranking for all badges for each site, so,
if the site was ranked 5 for BadgeA, and 10 for BadgeB, and so on, it would
have an average rank of 7.5 (or something).

We then order the sites by their average ranking, and re-rank them. The sites
which have generally high badge rankings will generally be highly ranked.

Sites which have 'spikes' on certain badges relative to other sites, will tend
to be 'smoothed' on the overall ranking.

So, This query loosely ranks the user-value available on each site in SE based
on an approximation of participation found in the badges that people are
awarded. Higher-ranked sites have generally got more people with more badges.

Note, this is a flawed query because it does not average things over time. It
is a 'current' concept.

*/

/*
  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),
        @bcount as int;

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

create table #sitestats (
    Site NVarchar(250),
    DName NVarchar(100),
    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 + '|Badge Link'',
                  ''' + @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,
           DName,
           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,
           DName,
           Avg(1.0 * Rank) as Overall
    from Rankings
    where BadgeName <> 'Caucus'
    group by Site, DName
)
insert into #sitestats
select Site,
       DName,
       'Rank',
       1 + (select count(*)
            from AvgRanks sub
            where sup.Overall > sub.Overall)
from AvgRanks sup
UNION
select tub.Site,
       tub.DName,
       'Score',
       Convert(int, tub.Overall * @bcount)
from AvgRanks tub
       
set @query = '
select DName as Site, [Rank], [Score], ' + @pivot + ', Site as [Badges]
from #sitestats as ss                          
PIVOT (SUM(ss.Awards) FOR ss.BadgeName IN ( [Rank], [Score], ' + @pivot + ' )
) AS BadgePivot
order by DName';

execute(@query)

fork query

edited jan 12 15

created jun 5 14 rolfl