Q&A about the site for aircraft pilots, mechanics, and enthusiasts
/* 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)