Stack Exchange Sites Question Voting Stats

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Adapted from: http://data.stackexchange.com/stackoverflow/query/199310/site-badge-rankings

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

CREATE TABLE #SiteVotes (
  Site NVarChar(250),
  TotalQuestions int,
  QuestionsAboveZero int,
  QuestionsAtZero int,
  QuestionsBelowZero int,
  QuestionsNull 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 #SiteVotes VALUES (''' + @sitedispname + ''',
           (SELECT COUNT(*)
           FROM [' + @sitedbname + ']..PostsWithDeleted WHERE PostTypeId = 1),
           (SELECT COUNT(*)
           FROM [' + @sitedbname + ']..PostsWithDeleted WHERE PostTypeId = 1 AND Score > 0),
           (SELECT COUNT(*)
           FROM [' + @sitedbname + ']..PostsWithDeleted WHERE PostTypeId = 1 AND Score = 0),
           (SELECT COUNT(*)
           FROM [' + @sitedbname + ']..PostsWithDeleted WHERE PostTypeId = 1 AND Score < 0),
           (SELECT COUNT(*)
           FROM [' + @sitedbname + ']..PostsWithDeleted WHERE PostTypeId = 1 AND Score IS NULL))
           '
           
          ; 
           
    print @query

    EXEC sp_executesql @query

    fetch next from SITES into @sitedbname
END

close SITES
deallocate SITES;

execute('
SELECT 
  ROW_NUMBER() OVER (ORDER BY CAST(QuestionsNull AS FLOAT) / TotalQuestions * 100 DESC) AS Rank
  , *
  , (CAST(QuestionsAboveZero AS FLOAT) / TotalQuestions * 100) As PercentAboveZero
  , (CAST(QuestionsAtZero AS FLOAT) / TotalQuestions * 100) As PercentAtZero
  , (CAST(QuestionsBelowZero AS FLOAT) / TotalQuestions * 100) As PercentBelowZero
  , (CAST(QuestionsNull AS FLOAT) / TotalQuestions * 100) As PercentNull
  FROM #SiteVotes
  ORDER BY PercentNull DESC
')

Enter Parameters

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