-10

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.

  • 1
    Why does this matter? The numbers are very small, and if employees are answering questions on the main sites, then they are doing that from their personal perspective, not as employees. – Martijn Pieters Jan 22 '15 at 13:05
  • 5
    Our jobs are not to answer questions on the sites. – Oded Jan 22 '15 at 13:06
  • Perhaps a better question would have been, how many employees does StackOverflow have, and into what professional areas are these organized? Also, are there any StackOverflow partners? Thanks. – John Sonderson Jan 22 '15 at 13:38
  • 5
    All the employees are listed here. Happy reading! – Shadow The Princess Wizard Jan 22 '15 at 13:43
  • Thanks, that answers my question. – John Sonderson Jan 22 '15 at 13:46
  • 1
    @Shadow if you're going to post that link please add "warning - do not hover mouse over images" in the future. – PeterJ Jan 22 '15 at 13:51
  • 2
    Not really into fun, are you @Peter? (guess you would like to downvote this feature request as well) ;) – Shadow The Princess Wizard Jan 22 '15 at 13:55
  • 3
    @Shadow I like fun but Jarrod Dixon's animation scares me. I'm never going outside again for fear of killer shrubs. – PeterJ Jan 22 '15 at 14:01
  • 1
    @JohnSonderson great question! It's interesting to know the fields of expertise of the SE employees. – One Face Jan 22 '15 at 14:27
  • Yes, interesting indeed. I also wonder whether there are any other employess/owners, such as Jeff Atwood (source: Wikipedia), who for some reason do not show up on the list. – John Sonderson Jan 22 '15 at 15:34
  • 3
    Jeff Atwood doesn't work at Stack Exchange (source: his profile). – mmyers Jan 22 '15 at 17:04
  • I've updated the contents of my question so that it is less uncler. – John Sonderson Jan 24 '15 at 14:02
3

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

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.

  • Thank you for your answer. Could you please explain to me the following: where are you running the code (locally or on a Stack Exchange server). Unless you're a SE employee yourself, I would have doubted one could create an SQL table on a remote SE database; and if you're creating the SQL code on a local machine, then I don't see any code that fetches the table data from SE public tables over the network (after all, AFAIK no SQL code will allow you to do such thing). So, how are you running your code, and were there / are there any other scriptlets involved in transferring the remote SE data? – John Sonderson Jan 25 '15 at 12:49
  • 1
    If you click the query link you end up in sede that can be used to query an offline copy of the databases of all sites. You could run your own local copy based on the datadump but that is inconvenient for answers provided here and basically the data is the same. – rene Jan 25 '15 at 12:54
  • 1
    And amazingly they allow us a lot to do on those databases. Look through some of the answers on sede to see some nice querying.... – rene Jan 25 '15 at 12:56
  • OK, now I see the Compose Query button in the link you posted. I see once I click there it is also possible to see the database schema in a div on the right-hand-side. I wonder what flavor of SQL can be used to issue queries MySQL/MSSQL/other? Thanks. – John Sonderson Jan 25 '15 at 13:06
  • 1
    T-Sql, it is a microsoft sql server (2012 I believe). You might want to checkout the data dictionary docs as well – rene Jan 25 '15 at 13:08
  • Thanks! Excellent code and very interesting information! :-) – John Sonderson Jan 25 '15 at 13:13
  • You may want to add some numeric data to your answer (so that other users seeing your answer can readily see the information relative to what my original question was asking). Thanks. – John Sonderson Jan 25 '15 at 13:15

You must log in to answer this question.

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