Create the "company" table from the new commit data tables.
diff --git a/commits/batch/create_query_tables.sql b/commits/batch/create_query_tables.sql
index 5c75d70..4b82884 100755
--- a/commits/batch/create_query_tables.sql
+++ b/commits/batch/create_query_tables.sql
@@ -20,12 +20,13 @@
# group by project, yearmonth
# order by yearmonth;
-drop table if exists ProjectCompanyActivity;
-create table ProjectCompanyActivity
- select project, company, count(distinct revision) as count
- from commits
- where date > NOW() - INTERVAL 3 MONTH and login != 'gerrit@eclipse.o'
- group by project, company;
+# Moved to ../capture/create_query_tables.sql
+#drop table if exists ProjectCompanyActivity;
+#create table ProjectCompanyActivity
+# select project, company, count(distinct revision) as count
+# from commits
+# where date > NOW() - INTERVAL 3 MONTH and login != 'gerrit@eclipse.o'
+# group by project, company;
# Moved to ../capture/create_query_tables.sql
#drop table if exists ProjectCommitterActivity;
diff --git a/commits/capture/create_query_tables.sql b/commits/capture/create_query_tables.sql
index 99b5108..4b8ed26 100755
--- a/commits/capture/create_query_tables.sql
+++ b/commits/capture/create_query_tables.sql
@@ -17,12 +17,24 @@
from GitRepo as r join GitCommit as c on r.path=c.path
group by project, period;
-#drop table if exists ProjectCompanyActivity;
-#create table ProjectCompanyActivity
-# select project, company, count(distinct revision) as count
-# from commits
-# where date > NOW() - INTERVAL 3 MONTH and login != 'gerrit@eclipse.o'
-# group by project, company;
+# Activity associated with a company. Note that we label those
+# commits that cannot be connected to a committer as being affiliated
+# with "[Committer]". Commit belonging to committers who are not
+# connected to a company are affilated with "[Unaffilated]".
+# Only the last three months of activity are considered.
+drop table if exists ProjectCompanyActivity;
+create table ProjectCompanyActivity
+ select
+ gr.project as project,
+ if(isnull(c.id), '[Contributor]', if(isnull(ca.orgName), '[Unaffiliated]', ca.orgName)) as company,
+ count(distinct gc.ref) as count
+ from GitRepo as gr
+ join GitCommit as gc on gr.path=gc.path
+ join GitCommitAuthor as gca on gc.ref=gca.ref
+ left join Committer as c on gca.email=c.email
+ left join CommitterAffiliation as ca on c.id=ca.id
+ where gc.date > now() - interval 3 month
+ group by gr.project, company;
# Not so much "committer" activity anymore; more like "author" activity.
drop table if exists ProjectCommitterActivity;
