3

Does anyone have a list of all Stack Exchange sites in order? (Based off of questions asked.)

I know Stack Overflow is the most popular in those terms.

  • 5
    stackexchange.com/sites#questions – Pang Jun 8 '16 at 2:39
  • @Pang doesn't really tell the exact order though. – TwentyCharMax Jun 8 '16 at 3:07
  • What exact order do you want? Use the "Sort by" dropdown on that page. – Pang Jun 8 '16 at 3:09
  • 1
    That link does order by the total number of questions, descending. It's perfectly in order. – Ben N Jun 8 '16 at 3:09
  • @ Ben N ok. Just forgot to click that. Thank you @Pang – TwentyCharMax Jun 8 '16 at 3:15
  • You may also be looking for how to number those sites after sorting them: meta.stackexchange.com/q/244812/215590 – PolyGeo Jun 8 '16 at 5:51
  • Can this be community wiki? – TwentyCharMax Nov 7 '16 at 19:00
5

You can use SEDE to answer that question. This query uses a multi-database procedure to visit each database and run some basic stats:

-- start create url from dbname
IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #siteurl;
GO
create procedure #siteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      -- we have three sites that have borked DNS entries
      set @dbname = (case @dbname
      when 'StackExchange.Audio' then 'StackExchange.Avp'
      when 'StackExchange.Audio.Meta' then 'StackExchange.Avp.Meta'
      when 'StackExchange.Mathoverflow.Meta' then 'net.Mathoverflow.meta'
      else @dbname
      end)
      -- and one of those doesn't want to end with .com
      if @dbname <> 'net.Mathoverflow.meta' 
         set @dbname = 'com.' + @dbname
      exec #recursesiteurl @dbname,  @res OUTPUT
   end
GO
-- create url from dbname
IF OBJECT_ID ( '#recursesiteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #recursesiteurl;
GO
create procedure #recursesiteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      declare @pos integer
      declare @rec nvarchar(250)

      set @res = @dbname
      set @pos = CHARINDEX('.', @dbname)
      if (@pos > 0 ) 
      begin
         set @rec = substring(@dbname, @pos+1, len(@dbname))
         exec #recursesiteurl @rec,  @rec output
         set @res = @rec 
                  + '.' 
                  + substring(@dbname, 0, @pos) 
      end
   end;
GO

-- all databases
declare  db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 5 -- skip master, temp, model, msdb, Data.SE
                         and ([name] not like '%.Meta' or [name] = 'StackExchange.Meta')

declare @db_c_name sysname   -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string

-- result table
create table #all_stats ( site nvarchar(250)
                            , qcount int
                            , acount int
                            , ccount int
                            , ucount int);

open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
begin
    set @sql = N'use '+ QUOTENAME(@db_c_name) +';
               declare @url nvarchar(250)
               exec #siteurl ''' + @db_c_name  + ''', @url output
               insert into #all_stats 
               select @url
               , (select count(*) from posts where posttypeid=1)
               , (select count(*) from posts where posttypeid=2)
               , (select count(*) from comments)
               , (select count(*) from users)
               ;'
    exec (@sql)
   fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;

select replace(
       replace(
       replace(site,
         '.StackExchange.com',''),
         '.com',''),
         '.net','') as [site]
     , 'http://' 
     + site 
     + '|'
     + site as link
     , qcount as [# Questions]
     , acount as [# Answers]
     , ccount as [# Comments]
     , ucount as [# Users]
from #all_stats
order by qcount desc

drop table #all_stats

drop procedure #recursesiteurl
drop procedure #siteurl

When run today the result looks like this:

enter image description here

Based on the suggestion of Peter Mortensen it would be more fair if a question rate was used, I assumed rate to mean Question/total days. That doesn't make a lot of difference:

qrate per total days

  • That is a little bit unfair to young sites compared to the eight-year-old gorilla. That is, scaling by site age could be interesting (essentially getting answer rates, question rates, etc.). It may not be easy to accomplish using SEDE, though. – Peter Mortensen Nov 7 '16 at 17:14
  • @PeterMortensen if that is just questions / #days site exists then it is not hard at all. – rene Nov 7 '16 at 17:33
2

As @Pang commented, you can visit All Sites - Stack Exchange and you can sort by

Traffic, Oldest, Newest, Questions, Answers, Percent Answered, Users, Questions Per Day, Name.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .