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
