(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),

weekly_activity AS

(SELECT DATE_TRUNC('week', updatedat) AS "Weeks", COUNT(DISTINCT author) AS "Number of Active Developers"

FROM raw_data

GROUP BY "Weeks"),

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

(SELECT COUNT(DISTINCT author) FROM raw_data) AS "Total Active Developers",

(SELECT AVG("Number of Active Developers") FROM weekly_activity WHERE "Weeks" ILIKE '%2022%') AS "Average # of Active Developers Per Week (2022)",

(SELECT AVG("Number of Active Developers") FROM weekly_activity WHERE "Weeks" ILIKE '%2021%') AS "Average # of Active Developers Per Week (2021)",

(SELECT "Number of Active Developers" FROM yearly_activity WHERE "Years" ILIKE '%2022%') AS "# of Active Developers(2022)",

(SELECT "Number of Active Developers" FROM yearly_activity WHERE "Years" ILIKE '%2021%') AS "# of Active Developers(2021)",

("# of Active Developers(2022)" - "# of Active Developers(2021)")*100/ "# of Active Developers(2021)" AS "YoY Growth in %"