SQLite 3.27

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

CREATE TABLE table1(
                window DATETIME,
                counter INTEGER NOT NULL DEFAULT 1,
                value INTEGER NOT NULL DEFAULT 0,
                file_id INTEGER NOT NULL,
                PRIMARY KEY(window,file_id)
            );
✓
INSERT INTO table1 (`window`, `file_id`, `counter`, `value`) VALUES
  ('2022-06-13 10:26:00', '1', '29', '3'),
  ('2022-06-13 10:27:00', '2', '99', '7'),
  ('2022-06-13 10:30:00', '1', '1', '22'),
  ('2022-06-13 10:31:00', '1', '1', '22'),
  ('2022-06-13 10:34:00', '1', '1', '22'),
  ('2022-06-13 10:39:00', '1', '1', '22'),
  ('2022-06-13 10:40:00', '1', '1', '22'),
  ('2022-06-13 10:53:00', '1', '1', '22');
✓
SELECT *, SUM(flag) OVER (ORDER BY window) grp
FROM (
  SELECT *, strftime('%s', window) - COALESCE(strftime('%s', LAG(window) OVER (ORDER BY window)), 0) > 600 flag
  FROM table1
);


window
counter
value
file_id
flag
grp


2022-06-13 10:26:00
29
3
1
1
1

2022-06-13 10:27:00
99
7
2
0
1

2022-06-13 10:30:00
1
22
1
0
1

2022-06-13 10:31:00
1
22
1
0
1

2022-06-13 10:34:00
1
22
1
0
1

2022-06-13 10:39:00
1
22
1
0
1

2022-06-13 10:40:00
1
22
1
0
1

2022-06-13 10:53:00
1
22
1
1
2


WITH cte AS (
  SELECT *, SUM(flag) OVER (ORDER BY window) grp
  FROM (
    SELECT *, strftime('%s', window) - COALESCE(strftime('%s', LAG(window) OVER (ORDER BY window)), 0) > 600 flag
    FROM table1
  )
)
SELECT grp,
       MIN(window) min_window,
       MAX(window) max_window,
       SUM(counter) sum_counter,
       SUM(value) sum_value
FROM cte
GROUP BY grp;       


grp
min_window
max_window
sum_counter
sum_value


1
2022-06-13 10:26:00
2022-06-13 10:40:00
133
120

2
2022-06-13 10:53:00
2022-06-13 10:53:00
1
22

window	counter	value	file_id	flag	grp
2022-06-13 10:26:00	29	3	1	1	1
2022-06-13 10:27:00	99	7	2	0	1
2022-06-13 10:30:00	1	22	1	0	1
2022-06-13 10:31:00	1	22	1	0	1
2022-06-13 10:34:00	1	22	1	0	1
2022-06-13 10:39:00	1	22	1	0	1
2022-06-13 10:40:00	1	22	1	0	1
2022-06-13 10:53:00	1	22	1	1	2