kaibladeWeekly Number of Issues Created Based on Activity State
Updated 2023-01-25
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
›
⌄
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