kaibladeYearly Active Developers on Github
    Updated 2023-01-25
    WITH raw_data AS
    (Select *, SPLIT(url, '/') AS split_url, split_url[ARRAY_SIZE(split_url)-2] AS action_type, mergecommit:oid AS commit_id
    FROM near.beta.github_activity),

    yearly_activity AS
    (SELECT DATE_TRUNC('year', updatedat) AS "Years", COUNT(DISTINCT author) AS "Number of Active Developers"
    FROM raw_data
    GROUP BY "Years")

    SELECT *,
    (CASE
    WHEN "Years" ILIKE '%2018%' THEN 'Year 2018'
    WHEN "Years" ILIKE '%2019%' THEN 'Year 2019'
    WHEN "Years" ILIKE '%2020%' THEN 'Year 2020'
    WHEN "Years" ILIKE '%2021%' THEN 'Year 2021'
    WHEN "Years" ILIKE '%2022%' THEN 'Year 2022'
    WHEN "Years" ILIKE '%2023%' THEN 'Year 2023'
    END) AS "Activity Year"

    FROM yearly_activity

    -- SELECT *
    -- FROM raw_data
    -- LIMIT 10000

    Run a query to Download Data