For each Stack Exchange site, what is the percentage of answers contributed by Stack Exchange employees?

Question

I would like to know, for each Stack Exchange site, out of all information available on each such site, what percentage of information (and in particular, number of answers) is provided by Stack Exchange employees (including contractor employees working outside the main SE headquarters/offices who may not work directly for Stack Exchange, i.e., employees not listed on the SE about/team page)?

I am expecting an answer such as, about 0.001%, for each site.

Answer 1

This is I guess basically the same thing as rene's answer except that a SQL Server database was most likely hurt during the making, and also the list of employees is included: Employee contributions by site.

---

Some summary info:

Top sites by employee question fraction:

StackExchange.Hermeneutics.Meta         19.70%
StackExchange.Sound.Meta                19.28%
StackExchange.Italian.Meta              16.67%
StackExchange.Workplace.Meta            11.84%
StackExchange.Patents.Meta              10.87%

Excluding metas (and StackApps):

StackExchange.Hermeneutics               6.65%
StackExchange.Beer                       2.92%
StackExchange.Skeptics                   2.09%
StackExchange.Sports                     1.75%
StackExchange.Politics                   1.45%

Top sites by employee answer fraction:

StackExchange.Patents.Meta              35.51%
StackOverflow.Ja.Meta                   29.01%
StackApps                               26.57%
StackExchange.Expatriates.Meta          24.85%
StackExchange.Hermeneutics.Meta         18.65%

Excluding metas (and StackApps):

StackExchange.Hermeneutics               3.94%
StackExchange.Skeptics                   3.18%
StackExchange.Italian                    2.39%
StackExchange.Expatriates                2.07%
StackExchange.Beer                       1.61%

Sites with no employee questions: 31 (including, to no-one's surprise, Windows Phone)

Sites with no employee answers: 20

Stack Overflow: 0.012% employee questions; 0.228% employee answers

Meta Stack Exchange: 1.43% employee questions; 14.17% employee answers

Answer 2

This won't be doing everything you asked for but it has all the ingredients to answer your question.

I'll be using this query. In normal operation mode The Stack Exchange Data Explorer, SEDE, will only query one specific site. Luckily the databases for all sites are in the same instance and can be queried.

I crafted a procedure that uses a cursor to iterate over all databases and run the following query to get the number of posts for a certain user:

select db_name()
     , u.id
     , count(*)
from posts p 
inner join users u on u.id = p.owneruserid 
where u.accountid in (1190) 
group by u.id

In the actual query we use a join on accountid with a temporary table. The accountid is found in the Users table which is different from the id per site but is stable accross sites. We can use that to correlate the users over mulitiple sites.

From the data-dictionary we learn that the users table does not have a column that indicates if a user is a Stack Exchange employee (or subcontractor, what ever that means to you). For this I added a temp table that we are populating with accountid's from Stack Exchange employees. I've done two as an example. You can add the remaining id's yourself, if you're interested, by forking my query.

Bringing this all together you get this script that stores its result in a temporary table called #emp_post_cnt and that one is selected at the end of the script.

create table #emp_acc (accountid integer);
-- add account id's here 
-- for example for Oded (select accountid from users where id = 1583)
insert into #emp_acc values(1190); -- Oded
insert into #emp_acc values(37099); -- Anna Lear

-- all databases
declare  db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 5 -- skip master, temp, model, msdb, Data.SE

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

-- result table
create table #emp_post_cnt ( db sysname
                            , displayname nvarchar(200)
                            , posts integer
                            , tot integer);
                            
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) +';
           insert into #emp_post_cnt 
           select db_name()
           , u.displayname 
           , count(*) 
           , (select count(*) from posts) 
           from posts p 
           inner join users u on u.id = p.owneruserid 
           inner join #emp_acc a on a.accountid = u.accountid 
           group by u.displayname;'
    exec (@sql)
   fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;

select db
     , displayname
     , posts 
     , tot
from #emp_post_cnt

drop table #emp_post_cnt
drop table #emp_acc

You can export this result to a csv file to do your statistical analysis in Excel or add grouping and calculations to the last select statement.

Keep in mind that queries are only allowed to run for about 2 minutes. Querying all databases takes time. If you hit those limits you'll need to separate your data gathering in batches and reconstruct/combine the data off-line.

During the making of this script no sql server database was hurt in any way.