author,

min(createdat) as first_commit,

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

from near.beta.github_activity

group by author

),

active_author as (

select date_trunc('month', createdat) as month,

array_unique_agg(author) as active_devs

from near.beta.github_activity

group by month

),

final as (select

month,

f.value as active_dev

from active_author,

table(flatten(active_author.active_devs)) f),

actives as (select

f.month,

f.active_dev,

c.start_year

from first_app c, final f

where c.author = f.active_dev),

monthly_active as (select

date_trunc('month', createdat) as date,

count(distinct(author)) as unique_developers

from near.beta.github_activity

group by date),