Stamatis,
Your "commits by non-committers" metric is a good one.
I think it might also be nice to call out, by name, the committers who
are committing non-committers' PRs. (Much more constructive than
shaming committers who are committing their own changes but no one
else's.)
Vladimir,
Very nice! You must be an old C programmer, using 1 and 0 for true and
false. May I suggest
select quarter_date
, count(*) filter (where is_committer) committers
, count(*) filter(where not is_commiter) non_committers
, count(*) total
from (
select cast(floor(commit_timestamp to quarter) as date) quarter_date
, c.author_timestamp > phonebook.first_commit as is_committer
from git_commits c
left join (
select committer
, min(commit_timestamp) first_commit
from git_commits
group by committer
) phonebook
on (c.author = phonebook.committer)
)
group by quarter_date
order by quarter_date;
Julian
On Fri, Oct 9, 2020 at 4:10 PM Vladimir Sitnikov
<[email protected]> wrote:
>
> You are doing it all wrong! :)
>
> model.json:
>
> {
> "version": "1.0",
> "defaultSchema": "os",
> "schemas": [
> {
> "name": "os",
> "tables": [
> {
> "name": "git_commits",
> "type": "view",
> "sql": "select * from table(\"git_commits\"(true))"
> }
> ],
> "functions": [
> {
> "name": "git_commits",
> "className": "org.apache.calcite.adapter.os.GitCommitsTableFunction"
> }
> ]
> }
> ]
> }
>
>
> $ ./sqlline
>
> sqlline> !connect jdbc:calcite:model=model.json;lex=java admin admin
>
> select quarter_date
> , sum(is_committer) committers
> , sum(1 - is_committer) non_committers
> , count(*) total
> from (
> select cast(floor(commit_timestamp to quarter) as date) quarter_date
> , case when c.author_timestamp > phonebook.first_commit then
> 1 else 0 end is_committer
> from git_commits c
> left join (
> select committer
> , min(commit_timestamp) first_commit
> from git_commits
> group by committer
> ) phonebook
> on (c.author = phonebook.committer)
> )
> group by quarter_date
> order by quarter_date;
>
>
> +--------------+------------+----------------+-----------------+
> | quarter_date | committers | non_committers | total |
> +--------------+------------+----------------+-----------------+
> | 2012-04-01 | 60 | 5 | 65 |
> | 2012-07-01 | 64 | 0 | 64 |
> | 2012-10-01 | 50 | 1 | 51 |
> | 2013-01-01 | 100 | 1 | 101 |
> | 2013-04-01 | 136 | 0 | 136 |
> | 2013-07-01 | 129 | 3 | 132 |
> | 2013-10-01 | 127 | 6 | 133 |
> | 2014-01-01 | 219 | 26 | 245 |
> | 2014-04-01 | 167 | 16 | 183 |
> | 2014-07-01 | 124 | 18 | 142 |
> | 2014-10-01 | 86 | 23 | 109 |
> | 2015-01-01 | 89 | 25 | 114 |
> | 2015-04-01 | 92 | 30 | 122 |
> | 2015-07-01 | 78 | 34 | 112 |
> | 2015-10-01 | 80 | 42 | 122 |
> | 2016-01-01 | 133 | 23 | 156 |
> | 2016-04-01 | 98 | 27 | 125 |
> | 2016-07-01 | 51 | 27 | 78 |
> | 2016-10-01 | 71 | 35 | 106 |
> | 2017-01-01 | 90 | 48 | 138 |
> | 2017-04-01 | 65 | 52 | 117 |
> | 2017-07-01 | 39 | 36 | 75 |
> | 2017-10-01 | 44 | 46 | 90 |
> | 2018-01-01 | 43 | 43 | 86 |
> | 2018-04-01 | 33 | 67 | 100 |
> | 2018-07-01 | 115 | 49 | 164 |
> | 2018-10-01 | 47 | 44 | 91 |
> | 2019-01-01 | 80 | 68 | 148 |
> | 2019-04-01 | 69 | 80 | 149 |
> | 2019-07-01 | 70 | 93 | 163 |
> | 2019-10-01 | 122 | 101 | 223 |
> | 2020-01-01 | 129 | 51 | 180 |
> | 2020-04-01 | 106 | 69 | 175 |
> | 2020-07-01 | 126 | 42 | 168 |
> | 2020-10-01 | 75 | 2 | 77 |
> +--------------+------------+----------------+-----------------+
> 35 rows selected (0.265 seconds)
>
>
> Vladimir
