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;