Reset release bugs SLA on reopen (#46312) (#46320)

Co-authored-by: Cursor <[email protected]>

1 files changed, 78 insertions, 11 deletions

diff --git a/.github/scripts/analytics/data_mart_queries/github_issues_timeline.sql b/.github/scripts/analytics/data_mart_queries/github_issues_timeline.sql
index cbece481272..89aa4c4cfbe 100644
--- a/.github/scripts/analytics/data_mart_queries/github_issues_timeline.sql
+++ b/.github/scripts/analytics/data_mart_queries/github_issues_timeline.sql
@@ -1,13 +1,13 @@
 -- 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.
+-- Open/closed state and SLA start from github_data/issue_open_periods (exported with issues).
+-- RECENT DAYS: updates only the last $recent_days days (31 by default). Use with data_mart_executor for quick refresh.
+-- Dates come from tests_monitor (date_window), no ListFromRange/FLATTEN BY for the date spine.
 -- 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 (
@@ -23,6 +23,67 @@ $owner_mapping = (
     WHERE rn = 1
 );
 
+$issue_periods = (
+    SELECT
+        p.project_item_id AS project_item_id,
+        p.issue_number AS issue_number,
+        p.period_start AS period_start,
+        p.period_end AS period_end
+    FROM `github_data/issue_open_periods` AS p
+    UNION ALL
+    SELECT
+        t2.project_item_id AS project_item_id,
+        t2.issue_number AS issue_number,
+        t2.created_date AS period_start,
+        Cast(t2.closed_at AS Date) AS period_end
+    FROM `github_data/issues` AS t2
+    LEFT JOIN (
+        SELECT DISTINCT
+            ep.project_item_id AS project_item_id,
+            ep.issue_number AS issue_number
+        FROM `github_data/issue_open_periods` AS ep
+    ) AS has_periods
+        ON has_periods.issue_number = t2.issue_number
+        AND has_periods.project_item_id = t2.project_item_id
+    WHERE has_periods.issue_number IS NULL
+);
+
+$issues_in_window = (
+    SELECT DISTINCT
+        ip.project_item_id AS project_item_id,
+        ip.issue_number AS issue_number
+    FROM $issue_periods AS ip
+    WHERE ip.period_start <= CurrentUtcDate()
+      AND (ip.period_end IS NULL OR ip.period_end >= CurrentUtcDate() - $timeline_days * Interval("P1D"))
+);
+
+$date_spine = (
+    SELECT DISTINCT date_window AS d
+    FROM `test_results/analytics/tests_monitor`
+    WHERE date_window >= CurrentUtcDate() - $timeline_days * Interval("P1D")
+);
+
+$open_on_day = (
+    SELECT
+        dt.d AS date,
+        ip.project_item_id AS project_item_id,
+        ip.issue_number AS issue_number,
+        ip.period_start AS sla_start_date
+    FROM $date_spine AS dt
+    CROSS JOIN $issue_periods AS ip
+    WHERE ip.period_start <= dt.d
+      AND (ip.period_end IS NULL OR ip.period_end > dt.d)
+);
+
+$closed_on_day = (
+    SELECT DISTINCT
+        ip.period_end AS date,
+        ip.project_item_id AS project_item_id,
+        ip.issue_number AS issue_number
+    FROM $issue_periods AS ip
+    WHERE ip.period_end IS NOT NULL
+);
+
 SELECT
     dt.d AS date,
     i.project_item_id AS project_item_id,
@@ -63,17 +124,14 @@ SELECT
     i.releaseblocker_state AS releaseblocker_state,
     i.branch AS branch,
     i.area AS area,
+    p.sla_start_date AS sla_start_date,
     CAST(
-        (i.closed_at IS NULL OR Cast(i.closed_at AS Date) > dt.d) AS Uint8
+        (p.sla_start_date IS NOT NULL) 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
+        (c.issue_number IS NOT NULL) 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
+FROM $date_spine AS dt
 CROSS JOIN (
     SELECT
         t.project_item_id AS project_item_id,
@@ -121,9 +179,18 @@ CROSS JOIN (
             END
         ) AS area
     FROM `github_data/issues` AS t
+    INNER JOIN $issues_in_window AS w
+        ON w.project_item_id = t.project_item_id AND w.issue_number = t.issue_number
     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
+LEFT JOIN $open_on_day AS p
+    ON p.date = dt.d
+    AND p.project_item_id = i.project_item_id
+    AND p.issue_number = i.issue_number
+LEFT JOIN $closed_on_day AS c
+    ON c.date = dt.d
+    AND c.project_item_id = i.project_item_id
+    AND c.issue_number = i.issue_number
 WHERE i.created_date <= dt.d
   AND dt.d >= CurrentUtcDate() - $recent_days * Interval("P1D");