(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('year', updatedat) AS "Years", COUNT(ID) AS "Number of Activities"

FROM raw_data

GROUP BY "Years")

SELECT *,

WHEN "Years" ILIKE '%2022%' THEN 'Year 2022'

WHEN "Years" ILIKE '%2021%' THEN 'Year 2021'

WHEN "Years" ILIKE '%2020%' THEN 'Year 2020'

WHEN "Years" ILIKE '%2019%' THEN 'Year 2019'

WHEN "Years" ILIKE '%2018%' THEN 'Year 2018'

END) AS years

FROM weekly_activity