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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
|
-- Issues on a daily timeline: for each date, which issues are open at end of day and which were closed that day.
-- RECENT DAYS: updates only the last $recent_days days (default 2). Use with data_mart_executor for quick refresh.
-- Dates come from tests_monitor (date_window), no ListFromRange/FLATTEN BY.
-- In BI: filter by date, owner_team; for issue list per day — filter by date; for counts — GROUP BY date, SUM(is_open_at_end_of_day), SUM(closed_on_this_day).
--
$timeline_days = 365;
$recent_days = 31; -- only these days are selected (today and $recent_days-1 days back)
-- Owner by area (prefix match): area/cs/analytics -> area/cs in mapping. Return matched_area (om.area) for output.
-- Distinct areas from source github_data/issues. New areas get owner/area from fallback in output.
$owner_mapping = (
SELECT area AS area, owner_team AS owner_team, matched_area AS matched_area
FROM (
SELECT
a.area AS area,
om.owner_team AS owner_team,
om.area AS matched_area,
ROW_NUMBER() OVER (PARTITION BY a.area ORDER BY LENGTH(om.area) DESC) AS rn
FROM (SELECT DISTINCT COALESCE(JSON_VALUE(info, "$.area"), 'area/-') AS area FROM `github_data/issues`) AS a
CROSS JOIN `test_results/analytics/area_to_owner_mapping` AS om
WHERE a.area = om.area OR StartsWith(a.area, om.area || '/')
)
WHERE rn = 1
);
SELECT
dt.d AS date,
i.project_item_id AS project_item_id,
i.issue_id AS issue_id,
i.issue_number AS issue_number,
i.title AS title,
i.url AS url,
i.state AS state,
i.state_reason AS state_reason,
i.created_at AS created_at,
i.updated_at AS updated_at,
i.closed_at AS closed_at,
i.created_date AS created_date,
i.updated_date AS updated_date,
i.author_login AS author_login,
i.author_url AS author_url,
i.repository_name AS repository_name,
i.repository_url AS repository_url,
i.project_status AS project_status,
i.project_owner AS project_owner,
i.project_priority AS project_priority,
i.is_in_project AS is_in_project,
i.days_since_created AS days_since_created,
i.days_since_updated AS days_since_updated,
i.time_to_close_hours AS time_to_close_hours,
i.assignees AS assignees,
i.labels AS labels,
i.milestone AS milestone,
i.project_fields AS project_fields,
i.info AS info,
i.issue_type AS issue_type,
i.exported_at AS exported_at,
i.owner_team AS owner_team,
i.labels_list AS labels_list,
i.max_branch AS max_branch,
i.env AS env,
i.priority AS priority,
i.branch AS branch,
i.area AS area,
CAST(
(i.closed_at IS NULL OR Cast(i.closed_at AS Date) > dt.d) AS Uint8
) AS is_open_at_end_of_day,
CAST(
(i.closed_at IS NOT NULL AND Cast(i.closed_at AS Date) = dt.d) AS Uint8
) AS closed_on_this_day
FROM (
SELECT DISTINCT date_window AS d
FROM `test_results/analytics/tests_monitor`
WHERE date_window >= CurrentUtcDate() - $timeline_days * Interval("P1D")
) AS dt
CROSS JOIN (
SELECT
t.project_item_id AS project_item_id,
t.issue_id AS issue_id,
t.issue_number AS issue_number,
t.title AS title,
t.url AS url,
t.state AS state,
t.state_reason AS state_reason,
t.created_at AS created_at,
t.updated_at AS updated_at,
t.closed_at AS closed_at,
t.created_date AS created_date,
t.updated_date AS updated_date,
t.author_login AS author_login,
t.author_url AS author_url,
t.repository_name AS repository_name,
t.repository_url AS repository_url,
t.project_status AS project_status,
t.project_owner AS project_owner,
t.project_priority AS project_priority,
t.is_in_project AS is_in_project,
t.days_since_created AS days_since_created,
t.days_since_updated AS days_since_updated,
t.time_to_close_hours AS time_to_close_hours,
t.assignees AS assignees,
t.labels AS labels,
t.milestone AS milestone,
t.project_fields AS project_fields,
t.info AS info,
t.issue_type AS issue_type,
t.exported_at AS exported_at,
COALESCE(m.owner_team, 'unknown') AS owner_team,
CAST(JSON_QUERY(t.labels, "$.name" WITH UNCONDITIONAL ARRAY WRAPPER) AS String) AS labels_list,
COALESCE(JSON_VALUE(t.info, "$.max_branch"), '-') AS max_branch,
COALESCE(JSON_VALUE(t.info, "$.env"), 'env:-') AS env,
COALESCE(JSON_VALUE(t.info, "$.priority"), 'priority:-') AS priority,
COALESCE(JSON_VALUE(t.info, "$.branch"), '-') AS branch,
Coalesce(m.matched_area,
CASE
WHEN ListLength(String::SplitToList(Cast(COALESCE(JSON_VALUE(t.info, "$.area"), 'area/-') AS String), '/')) >= 2
THEN String::SplitToList(Cast(COALESCE(JSON_VALUE(t.info, "$.area"), 'area/-') AS String), '/')[0] || '/' || String::SplitToList(Cast(COALESCE(JSON_VALUE(t.info, "$.area"), 'area/-') AS String), '/')[1]
ELSE COALESCE(JSON_VALUE(t.info, "$.area"), 'area/-')
END
) AS area
FROM `github_data/issues` AS t
LEFT JOIN $owner_mapping AS m ON m.area = COALESCE(JSON_VALUE(t.info, "$.area"), 'area/-')
WHERE t.created_date <= CurrentUtcDate()
AND (t.closed_at IS NULL OR Cast(t.closed_at AS Date) >= CurrentUtcDate() - $timeline_days * Interval("P1D"))
) AS i
WHERE i.created_date <= dt.d
AND dt.d >= CurrentUtcDate() - $recent_days * Interval("P1D");
|
