OssMetrics SQL access

examples.md

We use latest unmodified Postgres (9.5.4), here is SQL documentation https://www.postgresql.org/docs/current/static/

Example of connecting to our database using psql command, with credentials which you should receive in email:

# After running it will ask your for the password. 
# For scripting purpose you may set password using PGPASSWORD environment variable as well.
psql -h ossmetrics.com -U <user>

You have access to events_ex table, each row represents single event happening in your repository

 login         | character varying(255)
 created_at    | date                  
 repo_name     | character varying(255)
 repo_id       | integer               
 num           | character varying(40) 
 body          | text                  
 title         | text                  
 issue_opened  | smallint              
 issue_closed  | smallint              
 pr_opened     | smallint              
 pr_closed     | smallint              
 pr_merged     | smallint              
 code_pushes   | smallint              
 comments      | smallint              
 issue_comment | smallint              
 pr_comment    | smallint              
 watch         | smallint              
 fork          | smallint
 org           | boolean

With events_ex is should be easy to do aggregations, here is example of watch activity groped by week (note that it include both "stars" and "watch" actions)

SELECT date_trunc('week', created_at) AS week,
       SUM(watch)
FROM events_ex
GROUP BY date_trunc('week', created_at)
ORDER BY date_trunc('week', created_at);

Advanced example of generating opened vs closed issues/pr and currently opened issues:

WITH events_by_week AS
  ( SELECT date_trunc('week', created_at) AS week,
           SUM(issue_opened) AS issue_opened,
           SUM(pr_opened) AS pr_opened,
           SUM(issue_closed) AS issue_closed,
           SUM(pr_closed) AS pr_closed
   FROM events_ex
   GROUP BY date_trunc('week', created_at)
   ORDER BY date_trunc('week', created_at))
   
SELECT a.issue_opened + a.pr_opened opened,
       a.issue_closed + a.pr_closed closed,
       sum(a.issue_opened + a.pr_opened - a.issue_closed - a.pr_closed) OVER (ORDER BY week) AS currently_opened
FROM events_by_week a
ORDER BY a.week;

Example of generating community vs organization stats:

SELECT sum(pr_closed) all_closed,
       sum(pr_merged) all_merged,
       sum(pr_closed - pr_merged) all_closed_without_merge,
       sum(CASE
               WHEN org IS TRUE THEN pr_merged
               ELSE 0
           END) org_merged,
       sum(CASE
               WHEN org IS FALSE THEN pr_merged
               ELSE 0
           END) community_merged,
       sum(CASE
               WHEN org IS FALSE THEN pr_closed - pr_merged
               ELSE 0
           END) community_closed_no_merge
FROM events_ex;

If you addition you have access to the members table, which contains members of your github organization, so you can write your own logic if needed (exclude/remote members from stats).

events_ex is actually a view which use members table as well:

CREATE VIEW events_ex AS
SELECT events.*,
       (CASE WHEN members.login IS NULL THEN False ELSE True END) as org
FROM events
LEFT JOIN members ON members.login = events.login;