author,

count(*) as n_commits,

rank() over (order by n_commits desc) as rank

from near.beta.github_activity

where author not in ('dependabot', 'dependabot-preview')

group by author),

top100 as (select * from commits

order by n_commits desc

limit 20)

select

date_trunc('week', createdat) as date,

author,

count(*) as n_commits,

sum(n_commits) over (partition by author order by date asc rows between unbounded preceding and current row) as cumulative_commits

from near.beta.github_activity

where author in (select author from top100)

group by author, date