kaibladeWeekly Number of Issues Created Based on Activity State
    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)


    SELECT DATE_TRUNC('week', createdat) AS "Weeks",
    COUNT(id) AS "Number of Issues",
    state
    FROM raw_data
    WHERE action_type = 'issues'
    GROUP BY "Weeks", state


    -- weekly_activity AS
    -- (SELECT DATE_TRUNC('year', closedat) AS "Weeks", COUNT(ID) AS "Number of Merge Commits"
    -- FROM raw_data
    -- WHERE commit_id IS NOT NULL
    -- GROUP BY "Weeks")

    -- SELECT *,
    -- (CASE
    -- WHEN "Weeks" ILIKE '%2022%' THEN 'Year 2022'
    -- WHEN "Weeks" ILIKE '%2021%' THEN 'Year 2021'
    -- WHEN "Weeks" ILIKE '%2020%' THEN 'Year 2020'
    -- WHEN "Weeks" ILIKE '%2019%' THEN 'Year 2019'
    -- WHEN "Weeks" ILIKE '%2018%' THEN 'Year 2018'
    -- END) AS years
    -- FROM weekly_issues



    Run a query to Download Data