SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ID	N	START_DATE	STOP_DATE	GROUPS
1	1	2023-01-26	2023-04-13	0
1	2	2023-02-05	2023-04-13	0
1	3	2023-02-27	2023-04-13	0
1	4	2023-04-14	2023-09-13	1
1	5	2023-04-14	2023-09-13	1
1	6	2023-04-14	2023-09-13	1
3	1	2023-01-26	2023-04-13	0
3	2	2023-02-05	2023-04-13	0
3	3	2023-02-27	2023-04-13	0
3	4	2023-04-14	2023-09-13	0

ID	EVENT_HAPPENED
1	2023-03-12
1	2023-03-18
1	2023-04-07
1	2023-05-31
3	2023-01-31

WITH EventDates AS (
    SELECT ID, MIN(EVENT_HAPPENED) AS MIN_EVENT_DATE
    FROM Table2
    GROUP BY ID
),
IntervalDates AS (
    SELECT t1.ID, t1.N, t1.START_DATE, t1.STOP_DATE, t1.GROUPS,
        ed.MIN_EVENT_DATE AS HAPPENED_FIRST_TIME,
        ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t1.N DESC) 
        - ROW_NUMBER() OVER (PARTITION BY t1.ID, t1.GROUPS ORDER BY t1.N DESC) AS grp
    FROM Table1 t1
    LEFT JOIN EventDates ed ON t1.ID = ed.ID
),
closest_dates AS (
  SELECT ID, N, ROW_NUMBER() OVER (PARTITION BY ID, grp ORDER BY N DESC) AS RN
  FROM IntervalDates
  WHERE HAPPENED_FIRST_TIME BETWEEN START_DATE AND STOP_DATE
)
SELECT i.ID, i.N, i.START_DATE, i.STOP_DATE, i.GROUPS,
       CASE
          WHEN i.HAPPENED_FIRST_TIME <= i.START_DATE THEN 1
          ELSE 0
       END AS MARKER,
       CASE
         WHEN RN = 1 AND c.ID IS NOT NULL THEN 1
         ELSE 0
       END AS HAPPENED_FIRST_TIME,
       CASE
          WHEN RN = 1 AND c.ID IS NOT NULL THEN HAPPENED_FIRST_TIME
          ELSE NULL
       END AS HAPPENED_ON
FROM IntervalDates i
LEFT JOIN closest_dates c ON i.ID = c.ID and i.N = c.N

ID	N	START_DATE	STOP_DATE	GROUPS	MARKER	HAPPENED_FIRST_TIME	HAPPENED_ON
1	1	2023-01-26	2023-04-13	0	0	0	null
1	2	2023-02-05	2023-04-13	0	0	0	null
1	3	2023-02-27	2023-04-13	0	0	1	2023-03-12
1	4	2023-04-14	2023-09-13	1	1	0	null
1	5	2023-04-14	2023-09-13	1	1	0	null
1	6	2023-04-14	2023-09-13	1	1	0	null
3	1	2023-01-26	2023-04-13	0	0	1	2023-01-31
3	2	2023-02-05	2023-04-13	0	1	0	null
3	3	2023-02-27	2023-04-13	0	1	0	null
3	4	2023-04-14	2023-09-13	0	1	0	null