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

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

5 files changed, 496 insertions, 139 deletions

diff --git a/.github/scripts/analytics/data_mart_queries/datalens_ds_queries/github_issues_timeline_full.sql b/.github/scripts/analytics/data_mart_queries/datalens_ds_queries/github_issues_timeline_full.sql
index 9c2711e5c4e..7240606e214 100644
--- a/.github/scripts/analytics/data_mart_queries/datalens_ds_queries/github_issues_timeline_full.sql
+++ b/.github/scripts/analytics/data_mart_queries/datalens_ds_queries/github_issues_timeline_full.sql
@@ -1,114 +1,216 @@
--- For full reload via data_mart_executor_by_month.py. Issues on a daily timeline: for each date, which issues are open at end of day and which were closed that day.
--- Run: python3 .github/scripts/analytics/data_mart_executor_by_month.py --query_path .github/scripts/analytics/data_mart_queries/datalens_ds_queries/github_issues_timeline_full.sql --table_path test_results/analytics/github_issues_timeline --store_type column --partition_keys date --primary_keys date issue_number project_item_id
--- Optional: --by_month 12 (default)
--- FULL WINDOW: use with data_mart_executor (full 365-day window) or data_mart_executor_by_month (per-month).
--- 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).
---
--- Windows (change here or override via script):
---   $timeline_days   — date dimension and "open in window": include issues open on at least one day in [now - timeline_days, now].
---   Include: created_date <= today and (still open or closed within window: closed_at >= now - timeline_days).
-$timeline_days = 365;
--- For by_month wrapper: script overwrites these to restrict to one month (avoids connection timeouts)
-$month_start = Date("1970-01-01");
-$month_end = Date("2100-01-01");
-
-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.releaseblocker_state AS releaseblocker_state,
-    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, "$.releaseblocker_state"), 'release:-') AS releaseblocker_state,
-        COALESCE(JSON_VALUE(t.info, "$.branch"), '-') AS branch,
-        COALESCE(JSON_VALUE(t.info, "$.area"), 'area/-') AS area
-    FROM `github_data/issues` AS t
-    LEFT JOIN `test_results/analytics/area_to_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 >= $month_start AND dt.d < $month_end;
+-- For full reload via data_mart_executor.py. Issues on a daily timeline: for each date, which issues are open at end of day and which were closed that day.

+-- Open/closed state and SLA start from github_data/issue_open_periods (exported with issues).

+-- No query CTEs (DataLens-compatible): only scalar params + inline subqueries.

+-- Run: python3 .github/scripts/analytics/data_mart_executor.py --query_path .github/scripts/analytics/data_mart_queries/datalens_ds_queries/github_issues_timeline_full.sql --table_path test_results/analytics/github_issues_timeline --store_type column --partition_keys date --primary_keys date issue_number project_item_id --cleanup_window_key date --cleanup_window_interval '365 * Interval("P1D")'

+-- FULL WINDOW: 365-day reload; $month_start/$month_end can be narrowed manually if the query times out.

+--

+$timeline_days = 365;

+$month_start = Date("1970-01-01");

+$month_end = Date("2100-01-01");

+

+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.releaseblocker_state AS releaseblocker_state,

+    i.branch AS branch,

+    i.area AS area,

+    p.sla_start_date AS sla_start_date,

+    CAST(

+        (p.sla_start_date IS NOT NULL) AS Uint8

+    ) AS is_open_at_end_of_day,

+    CAST(

+        (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

+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, "$.releaseblocker_state"), 'release:-') AS releaseblocker_state,

+        COALESCE(JSON_VALUE(t.info, "$.branch"), '-') AS branch,

+        COALESCE(JSON_VALUE(t.info, "$.area"), 'area/-') AS area

+    FROM `github_data/issues` AS t

+    INNER JOIN (

+        SELECT DISTINCT

+            ip.project_item_id AS project_item_id,

+            ip.issue_number AS issue_number

+        FROM (

+            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

+        ) AS ip

+        WHERE ip.period_start <= CurrentUtcDate()

+          AND (ip.period_end IS NULL OR ip.period_end >= CurrentUtcDate() - $timeline_days * Interval("P1D"))

+    ) AS w

+        ON w.project_item_id = t.project_item_id AND w.issue_number = t.issue_number

+    LEFT JOIN `test_results/analytics/area_to_owner_mapping` AS m

+        ON m.area = COALESCE(JSON_VALUE(t.info, "$.area"), 'area/-')

+    WHERE t.created_date <= CurrentUtcDate()

+) AS i

+LEFT JOIN (

+    SELECT

+        dt_open.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 (

+        SELECT DISTINCT date_window AS d

+        FROM `test_results/analytics/tests_monitor`

+        WHERE date_window >= CurrentUtcDate() - $timeline_days * Interval("P1D")

+    ) AS dt_open

+    CROSS JOIN (

+        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

+    ) AS ip

+    WHERE ip.period_start <= dt_open.d

+      AND (ip.period_end IS NULL OR ip.period_end > dt_open.d)

+) 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 (

+    SELECT DISTINCT

+        ip.period_end AS date,

+        ip.project_item_id AS project_item_id,

+        ip.issue_number AS issue_number

+    FROM (

+        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

+    ) AS ip

+    WHERE ip.period_end IS NOT NULL

+) 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 >= $month_start AND dt.d < $month_end;

diff --git a/.github/scripts/analytics/data_mart_queries/datalens_ds_queries/github_issues_timeline_with_owner_from_mapping.sql b/.github/scripts/analytics/data_mart_queries/datalens_ds_queries/github_issues_timeline_with_owner_from_mapping.sql
index 35c7888076b..33008eeaf84 100644
--- a/.github/scripts/analytics/data_mart_queries/datalens_ds_queries/github_issues_timeline_with_owner_from_mapping.sql
+++ b/.github/scripts/analytics/data_mart_queries/datalens_ds_queries/github_issues_timeline_with_owner_from_mapping.sql
@@ -40,6 +40,7 @@ SELECT
     t.max_branch AS max_branch,
     t.env AS env,
     t.priority AS priority,
+    t.releaseblocker_state AS releaseblocker_state,
     t.branch AS branch,
     t.area AS area_full,
     Cast(CASE
@@ -54,11 +55,12 @@ SELECT
     END AS owner_team,
     t.is_open_at_end_of_day AS is_open_at_end_of_day,
     t.closed_on_this_day AS closed_on_this_day,
+    t.sla_start_date AS sla_start_date,
     CAST(
         CASE
-            WHEN t.priority LIKE '%low%' THEN DateTime::ToDays(Cast(t.date AS Date) - Cast(t.created_date AS Date)) < 30
-            WHEN t.priority LIKE '%med%' OR t.priority LIKE '%high%' THEN DateTime::ToDays(Cast(t.date AS Date) - Cast(t.created_date AS Date)) < 7
-            ELSE DateTime::ToDays(Cast(t.date AS Date) - Cast(t.created_date AS Date)) < 7
+            WHEN t.priority LIKE '%low%' THEN DateTime::ToDays(Cast(t.date AS Date) - Cast(COALESCE(t.sla_start_date, t.created_date) AS Date)) < 30
+            WHEN t.priority LIKE '%med%' OR t.priority LIKE '%high%' THEN DateTime::ToDays(Cast(t.date AS Date) - Cast(COALESCE(t.sla_start_date, t.created_date) AS Date)) < 7
+            ELSE DateTime::ToDays(Cast(t.date AS Date) - Cast(COALESCE(t.sla_start_date, t.created_date) AS Date)) < 7
         END AS Uint8
     ) AS in_sla
 FROM `test_results/analytics/github_issues_timeline` AS t
diff --git a/.github/scripts/analytics/data_mart_queries/github_issues_bugs_count_by_period.sql b/.github/scripts/analytics/data_mart_queries/github_issues_bugs_count_by_period.sql
index 6d46f7ecf87..d0ad1430ca2 100644
--- a/.github/scripts/analytics/data_mart_queries/github_issues_bugs_count_by_period.sql
+++ b/.github/scripts/analytics/data_mart_queries/github_issues_bugs_count_by_period.sql
@@ -30,6 +30,7 @@ $bugs_raw = (
         $normalize(t.area) AS area,
         t.project_item_id AS project_item_id,
         t.created_date AS created_date,
+        t.sla_start_date AS sla_start_date,
         t.priority AS priority
     FROM `test_results/analytics/github_issues_timeline` AS t
     WHERE t.date >= CurrentUtcDate() - $window_days * Interval("P1D")
@@ -73,7 +74,7 @@ $bugs = (
         b.project_item_id AS project_item_id,
         b.priority AS priority,
         o.owner_team AS owner_team,
-        DateTime::ToDays(Cast(b.date AS Date) - Cast(b.created_date AS Date)) AS days_open
+        DateTime::ToDays(Cast(b.date AS Date) - Cast(COALESCE(b.sla_start_date, b.created_date) AS Date)) AS days_open
     FROM $bugs_raw AS b
     LEFT JOIN $owner AS o ON b.area = o.area
 );
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");
diff --git a/.github/scripts/analytics/export_issues_to_ydb.py b/.github/scripts/analytics/export_issues_to_ydb.py
index 0dcbe6a04ce..05baecae122 100755
--- a/.github/scripts/analytics/export_issues_to_ydb.py
+++ b/.github/scripts/analytics/export_issues_to_ydb.py
@@ -8,7 +8,7 @@ import json
 import argparse
 from datetime import datetime, timezone, timedelta
 import requests
-from typing import List, Dict, Any, Optional
+from typing import List, Dict, Any, Optional, Tuple
 from ydb_wrapper import YDBWrapper
 
 ORG_NAME = 'ydb-platform'
@@ -165,8 +165,9 @@ def fetch_single_issue(org_name: str, repo_name: str, issue_number: int) -> Opti
             issueType {
               name
             }
-            timelineItems(last: 20, itemTypes: [CLOSED_EVENT]) {
+            timelineItems(last: 50, itemTypes: [CLOSED_EVENT, REOPENED_EVENT]) {
               nodes {
+                __typename
                 ... on ClosedEvent {
                   createdAt
                   actor {
@@ -174,6 +175,9 @@ def fetch_single_issue(org_name: str, repo_name: str, issue_number: int) -> Opti
                     login
                   }
                 }
+                ... on ReopenedEvent {
+                  createdAt
+                }
               }
             }
             projectItems(first: 30) {
@@ -287,8 +291,9 @@ def fetch_repository_issues(org_name: str = ORG_NAME, repo_name: str = REPO_NAME
               issueType {
                 name
               }
-              timelineItems(last: 20, itemTypes: [CLOSED_EVENT]) {
+              timelineItems(last: 50, itemTypes: [CLOSED_EVENT, REOPENED_EVENT]) {
                 nodes {
+                  __typename
                   ... on ClosedEvent {
                     createdAt
                     actor {
@@ -296,6 +301,9 @@ def fetch_repository_issues(org_name: str = ORG_NAME, repo_name: str = REPO_NAME
                       login
                     }
                   }
+                  ... on ReopenedEvent {
+                    createdAt
+                  }
                 }
               }
               projectItems(first: 30) {
@@ -507,7 +515,7 @@ def extract_last_close_actor(issue: Dict[str, Any]) -> Dict[str, Any]:
     event_at = None
     nodes = (issue.get('timelineItems') or {}).get('nodes') or []
     for event in reversed(nodes):
-        if not event:
+        if not event or event.get('__typename') != 'ClosedEvent':
             continue
         actor = event.get('actor') or {}
         cand_login = actor.get('login') or ''
@@ -519,6 +527,86 @@ def extract_last_close_actor(issue: Dict[str, Any]) -> Dict[str, Any]:
     return {'login': login, 'actor_type': actor_type, 'event_at': event_at}
 
 
+def build_open_periods(
+    issue: Dict[str, Any],
+    created_at: Optional[datetime],
+    closed_at: Optional[datetime],
+) -> List[Dict[str, Optional[str]]]:
+    """Build open intervals from GitHub close/reopen timeline events.
+
+    Each period is ``{"start": "YYYY-MM-DD", "end": "YYYY-MM-DD"|null}``.
+    ``end`` is the close date (issue is open through end-of-day before ``end``).
+    """
+    if created_at is None:
+        return []
+
+    events: List[tuple[str, datetime]] = []
+    for event in (issue.get('timelineItems') or {}).get('nodes') or []:
+        if not event:
+            continue
+        typename = event.get('__typename') or ''
+        event_at = parse_datetime(event.get('createdAt'))
+        if event_at is None:
+            continue
+        if typename == 'ClosedEvent':
+            events.append(('close', event_at))
+        elif typename == 'ReopenedEvent':
+            events.append(('reopen', event_at))
+
+    events.sort(key=lambda item: item[1])
+
+    periods: List[Dict[str, Optional[str]]] = []
+    open_start = created_at
+    is_open = True
+
+    for kind, event_at in events:
+        if kind == 'close' and is_open:
+            periods.append({
+                'start': open_start.date().isoformat(),
+                'end': event_at.date().isoformat(),
+            })
+            is_open = False
+        elif kind == 'reopen' and not is_open:
+            open_start = event_at
+            is_open = True
+
+    if is_open:
+        end = None
+        if (issue.get('state') or '').upper() == 'CLOSED' and closed_at is not None:
+            end = closed_at.date().isoformat()
+        periods.append({
+            'start': open_start.date().isoformat(),
+            'end': end,
+        })
+
+    return periods
+
+
+def open_period_rows_for_issue(
+    issue_number: int,
+    project_item_id: str,
+    open_periods: List[Dict[str, Optional[str]]],
+    exported_at: datetime,
+) -> List[Dict[str, Any]]:
+    rows = []
+    for idx, period in enumerate(open_periods):
+        period_start = datetime.fromisoformat(period['start']).date()
+        period_end = (
+            datetime.fromisoformat(period['end']).date()
+            if period.get('end')
+            else None
+        )
+        rows.append({
+            'issue_number': issue_number,
+            'project_item_id': project_item_id,
+            'period_index': idx,
+            'period_start': period_start,
+            'period_end': period_end,
+            'exported_at': exported_at,
+        })
+    return rows
+
+
 def projects_for_info_json(issue: Dict[str, Any]) -> List[Dict[str, Any]]:
     """Projects (v2) that contain this issue — id/title from GraphQL ``projectItems``."""
     out = []
@@ -577,8 +665,11 @@ def get_max_branch(branch_labels):
     return best
 
 
-def transform_issues_for_ydb(issues: List[Dict[str, Any]], project_fields: Optional[Dict[int, Dict[str, Any]]] = None) -> List[Dict[str, Any]]:
-    """Transform GitHub issues data for YDB storage"""
+def transform_issues_for_ydb(issues: List[Dict[str, Any]], project_fields: Optional[Dict[int, Dict[str, Any]]] = None) -> Tuple[List[Dict[str, Any]], List[Dict[str, Any]]]:
+    """Transform GitHub issues data for YDB storage.
+
+    Returns (issue_records, open_period_rows) for github_data/issues and github_data/issue_open_periods.
+    """
     print("Transforming issues data for YDB...")
     start_time = time.time()
     
@@ -586,6 +677,7 @@ def transform_issues_for_ydb(issues: List[Dict[str, Any]], project_fields: Optio
         project_fields = {}
     
     transformed_issues = []
+    open_period_rows: List[Dict[str, Any]] = []
     
     for issue in issues:
         # Get project fields for this issue if available
@@ -687,6 +779,8 @@ def transform_issues_for_ydb(issues: List[Dict[str, Any]], project_fields: Optio
         if closed_at:
             info['closed_at_iso'] = closed_at.isoformat()
 
+        open_periods = build_open_periods(issue, created_at, closed_at)
+
         now = datetime.now(timezone.utc)
         
         is_in_project = bool(issue_project_fields)
@@ -703,7 +797,6 @@ def transform_issues_for_ydb(issues: List[Dict[str, Any]], project_fields: Optio
         if closed_at and created_at:
             time_to_close_hours = int((closed_at - created_at).total_seconds() / 3600)
         
-        # Build the record
         issue_record = {
             # Primary identifiers
             'project_item_id': f"repo-{issue.get('number', 0)}",
@@ -758,10 +851,18 @@ def transform_issues_for_ydb(issues: List[Dict[str, Any]], project_fields: Optio
         }
         
         transformed_issues.append(issue_record)
+        open_period_rows.extend(
+            open_period_rows_for_issue(
+                issue_record['issue_number'],
+                issue_record['project_item_id'],
+                open_periods,
+                now,
+            )
+        )
     
     elapsed = time.time() - start_time
-    print(f"Transformed {len(transformed_issues)} issues (took {elapsed:.2f}s)")
-    return transformed_issues
+    print(f"Transformed {len(transformed_issues)} issues, {len(open_period_rows)} open periods (took {elapsed:.2f}s)")
+    return transformed_issues, open_period_rows
 
 def create_issues_table(ydb_wrapper: YDBWrapper, table_path: str):
     """Create issues table in YDB optimized for BI"""
@@ -838,6 +939,80 @@ def create_issues_table(ydb_wrapper: YDBWrapper, table_path: str):
     elapsed = time.time() - start_time
     print(f"BI-optimized table created successfully (took {elapsed:.2f}s)")
 
+
+def create_issue_open_periods_table(ydb_wrapper: YDBWrapper, table_path: str):
+    """Open/close intervals per issue for timeline and SLA."""
+    print(f"Creating issue open periods table: {table_path}")
+    create_sql = f"""
+        CREATE TABLE IF NOT EXISTS `{table_path}` (
+            `issue_number` Uint64 NOT NULL,
+            `project_item_id` Utf8 NOT NULL,
+            `period_index` Uint32 NOT NULL,
+            `period_start` Date NOT NULL,
+            `period_end` Date,
+            `exported_at` Timestamp NOT NULL,
+            PRIMARY KEY (`issue_number`, `project_item_id`, `period_index`)
+        )
+        PARTITION BY HASH(`issue_number`)
+        WITH (
+            STORE = COLUMN,
+            AUTO_PARTITIONING_BY_SIZE = ENABLED,
+            AUTO_PARTITIONING_PARTITION_SIZE_MB = 2048,
+            AUTO_PARTITIONING_MIN_PARTITIONS_COUNT = 4
+        )
+    """
+    ydb_wrapper.create_table(table_path, create_sql)
+
+
+def delete_issue_open_periods(ydb_wrapper: YDBWrapper, table_path: str, issue_numbers: List[int]) -> None:
+    if not issue_numbers:
+        return
+    for issue_number in issue_numbers:
+        query = f"""
+            DECLARE $issue_number AS Uint64;
+            DELETE FROM `{table_path}` WHERE issue_number = $issue_number;
+        """
+        ydb_wrapper.execute_dml(
+            query,
+            {
+                '$issue_number': ydb.TypedValue(
+                    value=int(issue_number),
+                    value_type=ydb.PrimitiveType.Uint64,
+                ),
+            },
+            query_name='delete_issue_open_periods',
+        )
+
+
+def upload_issue_open_periods(
+    ydb_wrapper: YDBWrapper,
+    table_path: str,
+    period_rows: List[Dict[str, Any]],
+    issue_numbers: List[int],
+    batch_size: int = 500,
+) -> None:
+    create_issue_open_periods_table(ydb_wrapper, table_path)
+    delete_issue_open_periods(ydb_wrapper, table_path, issue_numbers)
+    if not period_rows:
+        return
+    column_types = (
+        ydb.BulkUpsertColumns()
+        .add_column("issue_number", ydb.OptionalType(ydb.PrimitiveType.Uint64))
+        .add_column("project_item_id", ydb.OptionalType(ydb.PrimitiveType.Utf8))
+        .add_column("period_index", ydb.OptionalType(ydb.PrimitiveType.Uint32))
+        .add_column("period_start", ydb.OptionalType(ydb.PrimitiveType.Date))
+        .add_column("period_end", ydb.OptionalType(ydb.PrimitiveType.Date))
+        .add_column("exported_at", ydb.OptionalType(ydb.PrimitiveType.Timestamp))
+    )
+    ydb_wrapper.bulk_upsert_batches(
+        table_path,
+        period_rows,
+        column_types,
+        batch_size,
+        query_name='issue_open_periods',
+    )
+    print(f"Uploaded {len(period_rows)} rows to {table_path}")
+
 def main():
     """Main function to export GitHub issues to YDB"""
     parser = argparse.ArgumentParser(description='Export GitHub issues to YDB')
@@ -864,6 +1039,7 @@ def main():
         
         # Get table path from config
         table_path = ydb_wrapper.get_table_path("issues")
+        periods_table_path = ydb_wrapper.get_table_path("issue_open_periods")
         batch_size = 100
         
         try:
@@ -923,7 +1099,7 @@ def main():
                     project_fields = get_project_fields_for_issues(ORG_NAME, PROJECT_ID, issue_numbers)
             
             # Transform issues for YDB
-            transformed_issues = transform_issues_for_ydb(issues, project_fields)
+            transformed_issues, open_period_rows = transform_issues_for_ydb(issues, project_fields)
             
             # Upsert issues in batches using bulk_upsert_batches
             print(f"Uploading {len(transformed_issues)} issues in batches of {batch_size}")
@@ -1011,6 +1187,15 @@ def main():
             )
             
             ydb_wrapper.bulk_upsert_batches(table_path, transformed_issues, column_types, batch_size)
+
+            issue_numbers = [row['issue_number'] for row in transformed_issues]
+            upload_issue_open_periods(
+                ydb_wrapper,
+                periods_table_path,
+                open_period_rows,
+                issue_numbers,
+                batch_size=500,
+            )
             
             script_elapsed = time.time() - script_start_time
             print(f"Script completed successfully (total time: {script_elapsed:.2f}s)")