author,

min(createdat) as first_commit,

count(*) as n_commits,

year(date_trunc('year', first_commit)) as start_year

from near.beta.github_activity

group by author

),

n_authors_per_year as (select

start_year,

count(distinct(author)) as n_authors

from first_app

group by start_year),

commits_per_year as (select

start_year,

sum(n_commits) as total_commits

from first_app

group by start_year),

final as (select

*,

total_commits/n_authors as avg_commits_per_author,

case

when start_year = '2018' then 51

when start_year = '2019' then 49

when start_year = '2020' then 37

when start_year = '2021' then 25

when start_year = '2022' then 13

end as months_since_start,

avg_commits_per_author/months_since_start as avg_commits_per_author_per_month

from commits_per_year

join n_authors_per_year using (start_year))

select * from final