cypherNEAR - 9. Developer Activity - active and new
    Updated 2023-10-16
    with monthly_active as (select
    date_trunc('month', createdat) as date,
    count(distinct(author)) as unique_developers
    from near.beta.github_activity
    group by date),

    first_app as (select
    author,
    min(createdat) as first_commit
    from near.beta.github_activity
    group by author
    ),
    monthly_new_dev as (select
    date_trunc('month', first_commit) as date,
    count(*) as new_dev,
    sum(new_dev) over (order by date asc rows between unbounded preceding and current row) as total_devs
    from first_app
    group by date
    ),

    near_price as (select
    date_trunc('month', timestamp) as date,
    avg(price_usd) as avg_price
    from near.core.fact_prices
    where symbol = 'wNEAR'
    and date is not null
    group by date)


    select * from monthly_active
    join monthly_new_dev using (date)
    left join near_price using (date)

    -- limit 100
    Run a query to Download Data